Separating carriage return data into separate cells
 

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

Separating carriage return data into separate cells
 

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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Richard" wrote in message
...
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

Separating carriage return data into separate cells
 

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

"Richard" wrote:

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