Separating Two Data Sets from one Excel Cell into Two Cells

M

mpstockdale

I have data in an excel cell such as below:
*XXXX.XX.XX XXXX.XX.XX*
Sometimes it will appear as:
*XXXX.XX.XX yy XXXX.XX.XX*
The "yy" value is part of the first set of Xs.

It is actually two separate data sets. Is there a way to write
formula that will let me separate the data into two separate cells?

Thank you in advance for any assistance!

Mart
 
D

Dave O

The MID() function would work for you the number of columns is always
the same. Your example shows spaces before and after the yy, which you
could also use to your advantage in a SEARCH() function (as one of the
arguments in a MID function). Can you post a few actual examples?
 
M

mpstockdale

Thank you for your response.

Is this sufficient?


3926.90.20 3926.90.21
3926.90.20 pt 9021.90.81
3926.90.55 3924.90.56
3926.90.98 pt 8536.70.00
4010.13.00 4010.19.91


In all the lines above, the last 8 characters are one data set and th
other data set is comprised of either the first 8 characters or th
first 8 characters plus the following pt.

This is all contained in a single column. The goal is to ceate tw
columns...one with just the last 8 characters and the other with th
first 8 characters (and the following pt if present)
 
D

Dave Peterson

Is the text always "pt"?

If yes, then I'd select the column.
edit|replace
what: pt
with: (space bar)
replace all

Then use data|Text to columns to parse the contents into two cells.
 
M

mpstockdale

-Is the text always "pt"?-
Click to expand...

When there is text, it is pt, and it has to remain with the first se
of numbers, but the pt value is not seen much.

-
If yes, then I'd select the column.
edit|replace
what: pt
with: (space bar)
replace all
-
Click to expand...

How will that affect the "pt"?
-
Then use data|Text to columns to parse the contents into two cells.
-
Click to expand...


I apologize, but I don't know how to do this. Can you explai
further?

Thanks
 
R

Ron Rosenfeld

Thank you for your response.

Is this sufficient?


3926.90.20 3926.90.21
3926.90.20 pt 9021.90.81
3926.90.55 3924.90.56
3926.90.98 pt 8536.70.00
4010.13.00 4010.19.91


In all the lines above, the last 8 characters are one data set and the
other data set is comprised of either the first 8 characters or the
first 8 characters plus the following pt.

This is all contained in a single column. The goal is to ceate two
columns...one with just the last 8 characters and the other with the
first 8 characters (and the following pt if present).
Click to expand...


How about:

=LEFT(A1,LEN(A1)-11)

and

=RIGHT(A1,10)


--ron
 
D

Dave Peterson

I misunderstood. I thought that the PT stuff would be dropped.

Ron gave you formulas that work nicely.
 
M

mpstockdale

I feel *-_REALLY_-* dumb...I didn't even see Ron's response!

Ron--

Thank you for your review of my question and response. I will try i
out immediately.

Mart
 
Top