Splitting text and numbers from a cell
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
|