how do I remove line breaks from several cells?


J

JR

I have imported labels (formatted as first/last name, address, city/state/zip
with line breaks between in each cell) and need utilize the "text to colums"
in order to mail merge. How do I remove the line breaks so that the "text to
colums" can recognize the delimiters?

OR

How do I get excel to recognize a line break as a delimiter?

OR

Any other suggestions to mail merge this information?
 
Ad

Advertisements

S

Sheeloo

Use the formula
=SUBSTITUTE(A1,CHAR(10),"^")
to replace newline with ^ then use it as the delimiter

You can use any character in place of ^ as long as it is not likely be part
of your data
 
S

Sheeloo

Use the formula
=SUBSTITUTE(A1,CHAR(10),"^")
to replace newline with ^ then use it as the delimiter

You can use any character in place of ^ as long as it is not likely be part
of your data
 
J

JR

Where do I type this formula?


Sheeloo said:
Use the formula
=SUBSTITUTE(A1,CHAR(10),"^")
to replace newline with ^ then use it as the delimiter

You can use any character in place of ^ as long as it is not likely be part
of your data
 
J

JR

Where do I type this formula?


Sheeloo said:
Use the formula
=SUBSTITUTE(A1,CHAR(10),"^")
to replace newline with ^ then use it as the delimiter

You can use any character in place of ^ as long as it is not likely be part
of your data
 
S

Sheeloo

Assuming your data is in Col A then enter the formula in B1 and then copy
down...
You can then convert Col B to text
 
Ad

Advertisements

S

Sheeloo

Assuming your data is in Col A then enter the formula in B1 and then copy
down...
You can then convert Col B to text
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top