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 a
formula that will let me separate the data into two separate cells?

Thank you in advance for any assistance!

Marty


--
mpstockdale
------------------------------------------------------------------------
mpstockdale's Profile: http://www.officehelp.in/member.php?userid=4378
View this thread: http://www.officehelp.in/showthread.php?t=1222238

Posted from - http://www.officehelp.in

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?

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).


--
mpstockdale
------------------------------------------------------------------------
mpstockdale's Profile: http://www.officehelp.in/member.php?userid=4378
View this thread: http://www.officehelp.in/showthread.php?t=1222238

Posted from - http://www.officehelp.in

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.

mpstockdale wrote:

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).

--
mpstockdale
------------------------------------------------------------------------
mpstockdale's Profile: http://www.officehelp.in/member.php?userid=4378
View this thread: http://www.officehelp.in/showthread.php?t=1222238

Posted from - http://www.officehelp.in

--

Dave Peterson

-Is the text always "pt"?-

When there is text, it is pt, and it has to remain with the first set
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
-

How will that affect the "pt"?

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


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

Thanks!


--
mpstockdale
------------------------------------------------------------------------
mpstockdale's Profile: http://www.officehelp.in/member.php?userid=4378
View this thread: http://www.officehelp.in/showthread.php?t=1222238

Posted from - http://www.officehelp.in

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

Ron gave you formulas that work nicely.


mpstockdale wrote:

-Is the text always "pt"?-

When there is text, it is pt, and it has to remain with the first set
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
-

How will that affect the "pt"?

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

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

Thanks!

--
mpstockdale
------------------------------------------------------------------------
mpstockdale's Profile: http://www.officehelp.in/member.php?userid=4378
View this thread: http://www.officehelp.in/showthread.php?t=1222238

Posted from - http://www.officehelp.in

--

Dave Peterson

On Sun, 8 Oct 2006 01:04:08 +0530, mpstockdale
wrote:


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).


How about:

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

and

=RIGHT(A1,10)


--ron