How to change a Comma to a Carriage return in Excel

There have been many times that I have had a list of numbers separated by a comma, that I wanted to list vertically in an Excel spreadsheet, and had to use a very slow and tedious method to do this.

My original method was:

Copy the date, paste it into Notepad, then select each comma, and press enter.  This changed “5, 10, 15, 18, 21, 29” to
“5
10
15
18
21
29”

I then replaced all spaces with nothing. very easy, but tedious if more than just a few numbers.

There is an easier way that I found, but someone else probably has an even easier way, (feel free to tell me) 🙂

My new way:

I created a Macro called CommaToReturn changing the comma to the code Chr(10):

Sub CommaToReturn()
Cells.Replace What:=”,”, Replacement:=Chr(10), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

It isn’t perfect, and the result isn’t exactly what I want, as I then have to copy the result to notepad, and than back again to excel, after deleting any “ and ,
Maybe I can add that into the macro ?

If it works I will update this.