Separating Two Data Sets from one Excel Cell into Two Cells
 

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

Separating Two Data Sets from one Excel Cell into Two Cells
 

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?

Separating Two Data Sets from one Excel Cell into Two Cells
 

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

Separating Two Data Sets from one Excel Cell into Two Cells
 

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

Separating Two Data Sets from one Excel Cell into Two Cells
 

-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

Separating Two Data Sets from one Excel Cell into Two Cells
 

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

Separating Two Data Sets from one Excel Cell into Two Cells
 

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

Separating Two Data Sets from one Excel Cell into Two Cells
 

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 it
out immediately.

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

Separating Two Data Sets from one Excel Cell into Two Cells
 

Ron--

It worked perfectly. Thank you so much!!

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

Separating Two Data Sets from one Excel Cell into Two Cells
 

On Sun, 8 Oct 2006 08:32:26 +0530, mpstockdale
wrote:


Ron--

It worked perfectly. Thank you so much!!

Marty

Glad to help. And I guess you must have missed my "stealth" posting the first
time :-)


--ron