Splitting text and numbers from a cell

Hi,
I'm trying to split a cell that contains a part number and a part
description into a cell that contains the part number and a cell that
contains the part description. There are about 7,000 cells that need to be
separated this way. I tried the text to columns wizard but that is very
labor intensive since some part numbers have spaces and letters in them.
Example:
124 TD45 88 Ceramic Insulators
12 671 6773 Copper Coils
116011 Variable Resistors

I got as far as the formula =MID(A1,1,FIND(" ",A!,1)-1)
but that only returns the first group of letters ("124", "12", "116011") how
can I extract "124 TD45 88", "12 671 6773", and "116011" into their own cells
and "Ceramic Insulators", etc. into their own cells?

Thanks for your help,
Jim

Hi,

This works for your posted examples but relies on wanting to extract the
last 2 words.

For the last 2 words and note I put this in E1

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),199))

For the part No
=SUBSTITUTE(A1,E1,"")
This second formula is dependent on the first

Mike

"JimAA" wrote:

Hi,
I'm trying to split a cell that contains a part number and a part
description into a cell that contains the part number and a cell that
contains the part description. There are about 7,000 cells that need to be
separated this way. I tried the text to columns wizard but that is very
labor intensive since some part numbers have spaces and letters in them.
Example:
124 TD45 88 Ceramic Insulators
12 671 6773 Copper Coils
116011 Variable Resistors

I got as far as the formula =MID(A1,1,FIND(" ",A!,1)-1)
but that only returns the first group of letters ("124", "12", "116011") how
can I extract "124 TD45 88", "12 671 6773", and "116011" into their own cells
and "Ceramic Insulators", etc. into their own cells?

Thanks for your help,
Jim

Thanks! It worked. But what if I have more than two words in a part
description? i.e., "Ceramic Insulators, Coils and Resistors"

"Mike H" wrote:

Hi,

This works for your posted examples but relies on wanting to extract the
last 2 words.

For the last 2 words and note I put this in E1

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),199))

For the part No
=SUBSTITUTE(A1,E1,"")
This second formula is dependent on the first

Mike

"JimAA" wrote:

Hi,
I'm trying to split a cell that contains a part number and a part
description into a cell that contains the part number and a cell that
contains the part description. There are about 7,000 cells that need to be
separated this way. I tried the text to columns wizard but that is very
labor intensive since some part numbers have spaces and letters in them.
Example:
124 TD45 88 Ceramic Insulators
12 671 6773 Copper Coils
116011 Variable Resistors

I got as far as the formula =MID(A1,1,FIND(" ",A!,1)-1)
but that only returns the first group of letters ("124", "12", "116011") how
can I extract "124 TD45 88", "12 671 6773", and "116011" into their own cells
and "Ceramic Insulators", etc. into their own cells?

Thanks for your help,
Jim

But what if I have more than two words in a part description?

Then it won't work and I don't know how to do it.

Mike

"JimAA" wrote:

Thanks! It worked. But what if I have more than two words in a part
description? i.e., "Ceramic Insulators, Coils and Resistors"

"Mike H" wrote:

Hi,

This works for your posted examples but relies on wanting to extract the
last 2 words.

For the last 2 words and note I put this in E1

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),199))

For the part No
=SUBSTITUTE(A1,E1,"")
This second formula is dependent on the first

Mike

"JimAA" wrote:

Hi,
I'm trying to split a cell that contains a part number and a part
description into a cell that contains the part number and a cell that
contains the part description. There are about 7,000 cells that need to be
separated this way. I tried the text to columns wizard but that is very
labor intensive since some part numbers have spaces and letters in them.
Example:
124 TD45 88 Ceramic Insulators
12 671 6773 Copper Coils
116011 Variable Resistors

I got as far as the formula =MID(A1,1,FIND(" ",A!,1)-1)
but that only returns the first group of letters ("124", "12", "116011") how
can I extract "124 TD45 88", "12 671 6773", and "116011" into their own cells
and "Ceramic Insulators", etc. into their own cells?

Thanks for your help,
Jim

On Fri, 13 Nov 2009 10:04:05 -0800, JimAA
wrote:

Beautiful! Worked like a charm. Thank you for your help.

Glad to help. Thanks for the feedback.
--ron