If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
how do I remove line breaks from several cells?
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? |
#3
|
|||
|
|||
how do I remove line breaks from several cells?
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? |
#4
|
|||
|
|||
how do I remove line breaks from several cells?
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? |
Thread Tools | |
Display Modes | |
|
|