how do I remove line breaks from several cells?

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?

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

"JR" wrote:

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?

Where do I type this formula?


"Sheeloo" wrote:

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

"JR" wrote:

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?

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

"JR" wrote:

Where do I type this formula?


"Sheeloo" wrote:

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

"JR" wrote:

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?