Selecting only parts of a string of text.

[BlueDingo]

Hello, I am after some help in separating parts of a string of text to go into separate columns.<o></o>
<o> </o>
Fortunately the spreadsheet is in the same format in relation to spaces and numbers of characters with the exception of the red text which can be any where between 2 and 50 characters.<o></o>
<o> </o>
I actually need AL46 to go to Col B, 001488616 into Col C, Valve, Pressure Equalizing, Gaseous into Col D and D03079/0002 into Col E.
<o> </o>
<o> </o>
<o> </o>
(65) AL46 ALSPO 0299999999 A20LS DISPOSALS.(001488616) Valve, Pressure Equalizing, Gaseous.D03079/0002<o></o>
<o> </o>
Thank you Gurus one and all,<o></o>
Regards, Pete<o></o>

 

[BlueDingo]

OK, thanks for that, I'll accept the belt on the back on the head.

The following blue font, eg, 001488616 will not always be at the same location "as the text A20LS DISPOSAL can change to any number of characters".

 

[bschwartz]

Yes, we got that...

How about these questions?

For example, is the variable text A20LS DISPOSAL also a variable number of words? Does it always end in a period followed by an open parenthesis?

Also, a few more example text strings wouldn't hurt.

By the way, did my solution posted above work for you?

 

[BlueDingo]

As you can see by other row lines there are what one would expect in a logistics type environment:

(65) AL46 ALSPO 0299999999 A20LS DISPOSALS.(001488916) Valve, Pressure Equalizing, Gaseous.D03079/0002<o></o>

(69) AZ01 ALSPO G130 RAZH PROC 0799999999.(004901846) O-Ring.D13595/0001<o></o>

(6S) AT20 ALSPO H130HSIM AF 0299999999.(001898080) Thinner, Paint Products.E17030/0001 Your formula worked with some truncation, the third line resulted with the item name as "ner, Paint Products" Again thank you.

<TBODY>
</TBODY>

 

[bschwartz]

How about this:

Excel 2010
	A	B	C	D	E
1	(65) AL46 ALSPO 0299999999 A20LS DISPOSALS.(001488616) Valve, Pressure Equalizing, Gaseous.D03079/0002	AL46	001488616	Valve, Pressure Equalizing, Gaseous	D03079/0002
2	(69) AZ01 ALSPO G130 RAZH PROC 0799999999.(004901846) O-Ring.D13595/0001	AZ01	004901846	O-Ring	D13595/0001
3	(6S) AT20 ALSPO H130HSIM AF 0299999999.(001898080) Thinner, Paint Products.E17030/0001	AT20	001898080	Thinner, Paint Products	E17030/0001
Sheet4

Cell Formulas
Range	Formula
B1	=MID(A1, 6, 4)
C1	=MID(A1, SEARCH("^",SUBSTITUTE(A1,".","^",1))+2, 9)
D1	=MID(A1,SEARCH("^",SUBSTITUTE(A1,") ","^",2))+2,SEARCH(".",REPLACE(A1,1,SEARCH("^",SUBSTITUTE(A1,") ","^",2))+2,"")))
E1	=RIGHT(A1, 11)

 

[BlueDingo]

Perfect Ben, thanks for your help and patience,
Regards, Pete

 

[bschwartz]

You're very welcome, Pete.