How to change a Comma to a Carriage return in Excel

These Adverts help to pay for this site. ai2

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.


These Adverts help to pay for this site ai6.

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:


Agoda HolidaysAgoda HolidaysThese Adverts help to pay for this site. ai5

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.


200.1 - 587,960
DreamHost

Scroll down for Comments
0 0 votes
Article Rating
Subscribe
Notify of

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments