How can I split a cell that contains text split by the characters <CR> into seperate cells.

Hi,

I have 4500 address lines where the data is in the following format:

Accounts Department<CR>University of Nowhere<CR>Nowhere Lane<CR>London

That data is in cell A1.  I would like to split it out so that the data would read:

B1                                            C1                                   D2                   E2
Accounts Department     University of Nowhere  Nowhere Lane  London

The <CR> is actual text and not a system character

Many thanks,
Glenn
glennturner1Asked:
Who is Participating?
 
NBVCConnect With a Mentor Commented:
Try this:

Select the column and go to Data|Text to columns.

Select Delimited, then Next.

Select the Other checkbox and type a < character

Click Finish.

Now go to Home|Find & Replace|Replace (or CTRL+H)

FIND WHAT: CR>

REPLACE WITH: (nothing)  don't enter anything.

Click REPLACE ALL
0
 
als315Commented:
Can you upload sample and show expected result?
0
 
Ejgil HedegaardCommented:
Use replace Ctrl+H
Replace <CR> with | or another character that is not used in the text.
Perhaps character 255, Alt+255 on the numeric keypad.
Mark column A.
On the Data tab select 'Text to column' and insert | as the delimiter.
Then the result will be in columns A to D
0
 
glennturner1Author Commented:
Hi  NBVC,

That is exactly what I was after.

Thank you to others for your replies.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.