Substitute a comma with a line break in a cell

Question

In the cell below  I want to get whatever is separated by a comma to come to a new line  I can add these line breaks manually with alt enter  but this time I want to automate it      BCM Open Event Site XXXX is down Service Affected  2G Impact  Coverage   Restored at XXXX Area Reason Under Investigation Recovery Time  30   Minutes Start time 14 25 13 End Time 15 18 03 Duration 00 52 50 SLA 1   Hour

User · Answer

Use    SUBSTITUTE A1     CHAR 10   amp  CHAR 13     This will replace each comma with a new line  Change A1 to the cell you are referencing

User · Answer

For some reason  none of the above worked for me  This DID however    Selected the range of cells I needed to replace  Go to Home   Find  amp  Select   Replace or Ctrl   H Find what    Replace with  CTRL   SHIFT   J   Click Replace All   Somehow CTRL   SHIFT   J is registered as a linebreak

User · Answer

Windows  unlike some other OS s  like Linux   uses CR LF for line breaks    CR   13   0x0D    M    r   carriage return LF   10   0x0A    J    n   new line   The characters need to be in that order  if you want the line breaks to be consistently visible when copied to other Windows programs   So the Excel function would be    SUBSTITUTE A1     CHAR 13   amp  CHAR 10

User · Answer

You can also do this without VBA from the find replace dialogue box  My answer was at https   stackoverflow com a 6116681 509840

User · Answer

To replace commas with newline characters use this formula  assuming that the text to be altered is in cell A1     SUBSTITUTE A1     CHAR 10    You may have to then alter the row height to see all of the values in the cell  I ve left a comment about the other part of your question    Edit  here s a screenshot of this working - I had to turn on  Wrap Text  in the  Format Cells  dialog

[excel] Substitute a comma with a line break in a cell

Examples related to excel