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.
This question is related to
excel
The answer is
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 & Select > Replace or Ctrl + H
- Find what:
, - Replace with: CTRL + SHIFT + J
- Click
Replace All
Somehow CTRL + SHIFT + J is registered as a linebreak.
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.
You can also do this without VBA from the find/replace dialogue box. My answer was at https://stackoverflow.com/a/6116681/509840 .
Use
=SUBSTITUTE(A1,",",CHAR(10) & CHAR(13))
This will replace each comma with a new line. Change A1 to the cell you are referencing.
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) & CHAR(10))
Source: Stackoverflow.com