Separating carriage return data into separate cells

[Richard]

Separating carriage return data into separate cells

I imported data from a SQL database that has information stored in the cell
as information separated by carriage returns.

Keep in mind what is below is all on one cell:

This row will be random text and random length but there is information that
is valuable to me in this line of text
Name:JOE,SMITH
OrderNo:99999999
TotalSubmittedAmt:$999.99
PaidAmt:$99.99
ClaimDate:99/99/9999

I need to separate these fields into separate cells so I can use the data.
On the bottom 4 lines I know that the text will be identical but the first
line of text will vary depending. The results that I am looking for are
something like the following.

Text Name OrderNo TotalSubmittedAmt PaidAmt Date
First line JOE,SMITH 99999999 999.99 99.99 99/99/9999

 

[RagDyer]

Try Text To Columns.

Select the column of data,
From the Menu Bar:
<Data> <TextToColumns> <Delimited> <Next>

In the next window of the wizard, click on "Other",
Then hit
<Ctrl> <J>

You should see your data separated in the "Preview Window".

Click <Finish>.

 

[Sheeloo]

If your values are in Col A then enter this in B1
=SUBSTITUTE(A1,CHAR(10),"^")
and copy down
Now select Col B, press CTRL-C to copy, right-click and choose Paste
Special-> Values
Now choose DATA->Text to Columns
Choode delimted and delimiter as ^

You can then find column labels and replace with nothing