Search and find in a string of text

[Dojorgen]

Hey guys hoping you could help me out with this:

String of text:
'CMD/A="RES_ACTIVATE" MID/A="vfei2W0206" MTY/A="C" TID/U4=373 TYPE/A="RECIPE" RES_ID/A="E.299.30" PORT_ID/B=1 MBC_ID1/A="419253901.000" MBC_ID2/A="BLANK"'

Search for:
Line of text looking to pull search for "RECIPE"

If true then results:
then results the "E.299.30"
and next column "419253901.000"

 

[Scott Huish]

Excel 2010
	A	B	C
1	CMD/A="RES_ACTIVATE" MID/A="vfei2W0206" MTY/A="C" TID/U4=373 TYPE/A="RECIPE" RES_ID/A="E.299.30" PORT_ID/B=1 MBC_ID1/A="419253901.000" MBC_ID2/A="BLANK	"E.299.30"	"419253901.000"
Sheet1

Cell Formulas
Range	Formula
B1	=IFERROR(TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("RES_ID/A",A1)+9,255)," ",REPT(" ",255)),255))&T(FIND("RECIPE",A1)),"")
C1	=IFERROR(TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("MBC_ID1/A",A1)+10,255)," ",REPT(" ",255)),255))&T(FIND("RECIPE",A1)),"")

 

[Dojorgen]

sorry should have been more specific instead of "E.299.30" and "419253901.000" just have it so it is E.299.30 and 419253901.000

 

[jtakw]

Hi,

You've Only shown one sample text string without much of a description...
If "RECIPE" is found, will there Always be 2 substrings to extract?
If so, are they Always following RES_ID/A, and MBC_ID1/A?
Will there be a valid value in MBC_ID2/A (or more, i.e. MBC_ID3/A, MBC_ID4/A, etc.) you might want extracted?

Edit: Perhaps you should show a few more samples.

 

[Dojorgen]

Yes thank you for asking if "RECIPE" is found there will always be 2 sub strings to extract it works perfect i was hoping just get ride of the quotes

 

[Scott Huish]

Excel 2010
	A	B	C
1	CMD/A="RES_ACTIVATE" MID/A="vfei2W0206" MTY/A="C" TID/U4=373 TYPE/A="RECIPE" RES_ID/A="E.299.30" PORT_ID/B=1 MBC_ID1/A="419253901.000" MBC_ID2/A="BLANK	E.299.30	419253901.000
Sheet1

Cell Formulas
Range	Formula
B1	=SUBSTITUTE(IFERROR(TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("RES_ID/A",A1)+9,255)," ",REPT(" ",255)),255))&T(FIND("RECIPE",A1)),""),"""","")
C1	=SUBSTITUTE(IFERROR(TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("MBC_ID1/A",A1)+10,255)," ",REPT(" ",255)),255))&T(FIND("RECIPE",A1)),""),"""","")

 

[Rick Rothstein]

....i was hoping just get ride of the quotes

You could just SUBSTITUTE them away...

=IFERROR(TRIM(SUBSTITUTE(LEFT(SUBSTITUTE(MID(A1,FIND("RES_ID/A",A1)+9,255)," ",REPT(" ",255)),255),"""",""))&T(FIND("""RECIPE""",A1)),"")

=IFERROR(TRIM(SUBSTITUTE(LEFT(SUBSTITUTE(MID(A1,FIND("MBC_ID1/A",A1)+10,255)," ",REPT(" ",255)),255),"""",""))&T(FIND("RECIPE",A1)),"")

 

[Dojorgen]

Thanks for all the great responses works great.......there is about 50K lines of text and i did find a "MBC_ID1/A" and a "MBC_ID2/A" if someone would like to help add that to the string that would be great appreciated all the help you guys have always been great

'CMD/A="RES_ACTIVATE" MID/A="vfei2W0206" MTY/A="C" TID/U4=419 TYPE/A="RECIPE" RES_ID/A="E.242.50" PORT_ID/B=2 MBC_ID1/A="BLANK" MBC_ID2/A="419260011.000"'

 

[jtakw]

See again Post # 4.

In the Single sample you gave in OP, and the sample in Post #8 , what happens when the value is "BLANK"?

 

[Dojorgen]

Looks like two different scenarios, for now using the above search "MBC_ID1/A it either displays 419253901.000 or BLANK. It would only be either or never both so the formula would need to FIND: MBC_ID1/A with criteria IF: "BLANK" FIND: MBC_ID2/A

'CMD/A="RES_ACTIVATE" MID/A="vfei2W0206" MTY/A="C" TID/U4=373 TYPE/A="RECIPE" RES_ID/A="E.299.30" PORT_ID/B=1 MBC_ID1/A="419253901.000" MBC_ID2/A="BLANK"'
'CMD/A="RES_ACTIVATE" MID/A="vfei2W0206" MTY/A="C" TID/U4=419 TYPE/A="RECIPE" RES_ID/A="E.242.50" PORT_ID/B=2 MBC_ID1/A="BLANK" MBC_ID2/A="419260011.000"'