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>

 

[mikerickson]

If that is in A1,
=MID(A1, 6, 4) returns "AL46"
=MID(A1, 44, 9) returns "001488616"
=RIGHT(A1, 11) returns "D03079/0002"

 

[bschwartz]

If that is in A1,
=MID(A1, 6, 4) returns "AL46"
=MID(A1, 45, 9) returns "001488616"
=RIGHT(A1, 11) returns "D03079/0002"

I think you meant 45 above?

Also to round this out, assuming the periods always appear in the same place you can use this formula for D1:

=MID(A1,56,SEARCH("^",SUBSTITUTE(A1,".","^",2)))

 

[Drrellik]

does the text in red change. or does it always start with Valve... and for that matter does the AL or any other formatting change. adding on to what Mikerickson gave you, you can also use Find() along with the MID() and Right() to help you with the 2-50 chars in red.

 

[bschwartz]

does the text in red change. or does it always start with Valve... and for that matter does the AL or any other formatting change. adding on to what Mikerickson gave you, you can also use Find() along with the MID() and Right() to help you with the 2-50 chars in red.

I think you answered your own question?

 

[Drrellik]

=MID(A1,FIND("V",A1),(FIND(".D",A1)-FIND("V",A1))) is what I was talking about as long as the Valve and or the .D did not change just its number place in the grand scheme of things. DR

 

[Michael M]

Rural Sydney.????....where are you Strathfield......
This function will pull the text out, but I'm guessing the descriptions will be vastly different...

Function NCC(s As String) As String
With CreateObject("VBScript.RegExp")
  .Pattern = "([A-z,]{3,}\s[A-z]{3,}\s[A-z,]{3,}\s[A-z]{3,}\W)"
  If .Test(s) Then NCC = .Execute(s)(0).SubMatches(0)
End With
End Function

 

[BlueDingo]

Thank you all for your responses however, perhaps I have not explained the detail concisely enough.

The first blue font, eg, AL46 will always begin at the 6th place that includes the space.
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.
The red font, eg, Valve, Pressure Equalizing, Gaseous can also be any number of characters in length, (could even read "Nut, brown rusty" however, it will always end at the 13th character (includes period) from the right.
The remaining blue font, eg, D03079/0002 is always 11 characters long and is to the end of the string of text.

I really appreciate all your efforts: , mikerickson, bschwartz, drrellik and Michael M from Western NSW.

 

[bschwartz]

Well I definitely agree that you did not explain the details well, since you said the only section with variable character count was the one in red. That being said, it would be more helpful to point out the patterns in the text strings than the challenges.

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.

 

[bschwartz]

Assuming the two periods are always there, does this work?

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
Sheet4

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