Hi! I have a data In my cell B2 (column name Address)
Albany Medical College
47 New Scotland Avenue MC70
Albany NY, 12208
Ph: (612) 239-6115
Fax: (612) 239-6118
muns@mail.amc.ed
I want to split the City / Phone / Fax / email to different columns,
Please any help is much appreciated.
A sample of my spreadsheet is attached.
I have tried the formulas from some of the threads but no luck.
Thanks in advance.
Rev12
Try this
- Clear columns C to H
- In Cell C2 use this formula
=SUBSTITUTE(B2,CHAR(10),"!")
- Copy down all the way
- Copy Column C and 'paste special' -- 'values'
- Select Range C2:C93
- 'Data' ribbon -- text to columns -- Delimited -- Other -- Type ! -- Finish
Life's a spreadsheet, Excel!
Say thanks, Click *
separator: press Ctrl+j
in English
- 'Data' ribbon -- text to columns -- Delimited -- Other -- press Ctrl+j -- Finish
Last edited by tim201110; 06-01-2018 at 10:56 AM.
Thanks for the help, appreciated.
Re: How to split Text in a Cell with line break to different columns
if you're able to use PowerQuery look at this one
btw. some phones are not separated by LF in your source so text2columns isn't working well
Last edited by sandy666; 06-01-2018 at 12:59 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks