Format Cell Contents

by

Because I wanted to see if I could do it with a formula:

=LEFT(IF(OR(ISERROR(--LEFT(A1)),AGGREGATE(14,6,ROW(INDIRECT("1:" & LEN(A1)))/(ISNUMBER(--MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1))),1)-AGGREGATE(15,6,ROW(INDIRECT("1:" & LEN(A1)))/(ISNUMBER(--MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1))),1)=3),LEFT(A1)," ") &REPT(" ",MAX(2 - (AGGREGATE(14,6,ROW(INDIRECT("1:" & LEN(A1)))/(ISNUMBER(--MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1))),1)-AGGREGATE(15,6,ROW(INDIRECT("1:" & LEN(A1)))/(ISNUMBER(--MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1))),1)),0)) & RIGHT(MID(A1,AGGREGATE(15,6,ROW(INDIRECT("1:" & LEN(A1)))/(ISNUMBER(--MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1))),1),AGGREGATE(14,6,ROW(INDIRECT("1:" & LEN(A1)))/(ISNUMBER(--MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1))),1)-AGGREGATE(15,6,ROW(INDIRECT("1:" & LEN(A1)))/(ISNUMBER(--MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1))),1)+1),3) & MID(A1,AGGREGATE(14,6,ROW(INDIRECT("1:" & LEN(A1)))/(ISNUMBER(--MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1))),1)+1,LEN(A1))&"      ",6)

enter image description here

Just to show, I replaced all the spaces in the formula with -.

enter image description here

More Related Contents:

  1. How do you change the value of a cell in excel by clicking on it using VBA? [closed]
  2. Cutting letters from alphanumeric cell entry, pasting to another cell
  3. Why MS Excel crashes and closes during Worksheet_Change Sub procedure?
  4. How to copy a line in excel using a specific word and pasting to another excel sheet?
  5. How can I send an HTTP POST request to a server from Excel using VBA?
  6. Formatting MM/DD/YYYY dates in textbox in VBA
  7. Delete row based on partial text
  8. Using VBA in Excel to Google Search in IE and return the hyperlink of the first result
  9. How to put a tooltip on a user-defined function
  10. VBA Shell function in Office 2011 for Mac
  11. How to copy sheets to another workbook using vba?
  12. Check if value exists in column in VBA
  13. Excel vba refresh wait
  14. hiding formulas in formula bar
  15. How to delete multiple rows without a loop in Excel VBA
  16. Excel vba to create every possible combination of a Range
  17. VBA Many buttons point to the same _Click sub
  18. How to suppress Update Links warning?
  19. How do we use restful APIs from Excel macros (vba)?
  20. Run a Macro from Another Workbook
  21. Sorting a dictionary by key in VBA
  22. Handle “No cells were found” error when filtered range is empty
  23. VBA paste as values – how to
  24. What are the benefits of using Classes in VBA? [closed]
  25. Writing a VLOOKUP function in vba
  26. Generate Word Documents (in Excel VBA) from a series of Document Templates
  27. Handle cancellation of InputBox to select range
  28. Using Excel VBA Macro To Capture + Save Screenshot of Specific Area In Same File
  29. Type Mismatch in Loop to scan Outlook Messages [duplicate]
  30. VBA: How to combine two ranges on different sheets into one, to loop through

Leave a Comment