Find and extract data from String

[wisemank]

Hi there, I have tried the formula =LEFT(M2,FIND("Part",M2)-1) to pull the part number I need out of the text in the cell, but I get different errors. I have three different text string conditions, they are as follows; Case1: string= Tooling for T141120001 ,MY 14 (I need the T141120001. Case 2: string= Tooling for 68104645AA ,MY 15. I need 68104645AA. Case 3: string= CN 21009S22 For Part 04726367AC MY 14 and I need 0472636AC. Any ideas?

 

[skorpionkz]

hmmm.. try this

Excel 2010
	A	B
1	Tooling for T141120001 ,MY 14	T141120001
2	Tooling for 68104645AA ,MY 15	68104645AA
3	CN 21009S22 For Part 04726367AC MY 14	04726367AC
Sheet1

Cell Formulas
Range	Formula
B1	=IF(ISERROR(FIND("Tooling",A1)),TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),320,150)),TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),120,150)))

 

[wisemank]

Works great...Now to understand for future! Many thanks

 

[Rick Rothstein]

If the text you want to retrieve is always the third "word" in from the end of the text (as your three examples show) then you can use this shorter formula (6 less function calls)...

=TRIM(LEFT(RIGHT(SUBSTITUTE(" "&A1," ",REPT(" ",99)),297),99))

 

[skorpionkz]

Works great...Now to understand for future! Many thanks

Rick's formula is better(if the Part Number is always 3rd word from the end as Rick mentioned), and is easier to understand as well.