Parsing line breaks into separate cells

[JaneA28206]

I have exported Outlook Contacts to Excel. The data in the "BusinessStreet"
field contains 'carriage returns'. I would like to non-manually split the
data into columns. I other words, I'd like a Suite number that appears on the
second line of the Outlook address to be in a separate column (cell). For
example, what exports into a single cell is "123 Front St||Suite A". I'd like
"123 Front St" to appear in one cell and "Suite A" to appear in a separate
cell. I've tried using Text to Columns, but to no avail. HELP! Thanks!!!

 

[Jacob Skaria]

A small trick using Data>Text to Columns....

--By default the selection is 'Delimited'. Keep the selection and hit 'Next'.
--From the Step2 of the Wizard from the options select 'Other'
--In other hit the key combination Ctrl+j (Control key + j)
--Hit Finish

If this post helps click Yes

 

[BSc Chem Eng Rick]

Data-->Data Tools, click "Text to Columns". Select delimited-->Next then try
using the "Space" as the delimiter. If that doesn't work, click other and
then Ctrl+j (I think that is the carriage return code.

If this helps, please click "Yes"
<><><><><><><><><><><>

 

[Jacob Skaria]

Using formula:
With data in cell A1 copy the below formula to cell B1 and copy across as
required

=TRIM(MID(SUBSTITUTE(CHAR(10) & $A$1&
REPT(CHAR(10),6),CHAR(10),REPT(CHAR(32),255)),COLUMNS($B$1:B$1)*255,255))

If this post helps click Yes