?
Solved

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

Posted on 2014-01-24
4
Medium Priority
?
328 Views
Last Modified: 2014-01-24
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
0
Comment
Question by:glennturner1
4 Comments
 
LVL 40

Expert Comment

by:als315
ID: 39807211
Can you upload sample and show expected result?
0
 
LVL 23

Accepted Solution

by:
NBVC earned 1200 total points
ID: 39807223
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
 
LVL 24

Expert Comment

by:Ejgil Hedegaard
ID: 39807234
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
 

Author Closing Comment

by:glennturner1
ID: 39807251
Hi  NBVC,

That is exactly what I was after.

Thank you to others for your replies.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question