Extracting text/numbers after a specific text

[silverkay]

Hi all,

I am looking for help with regard to extracting text/numbers after a specific text in Excel, below are two examples

"Manufacturer Silver Tire Width 205 Dia 21 Qty 1"
"Manufacturer Rubber Tire Width 213 Dia 23 Qty 3"

I am looking to extract
1) Text after "Manufacturer", e.g. "Silver" or "Rubber"
2) Number after Width, e.g. 205 or 213
3) Number after Dia, e.g. 21 or 23
4) Number after Qty. 1 or 3

Any help will be greatly appreciated, thank you!

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E
1		Manufacturer	Width	Dia	Qty
2	Manufacturer Silver Tire Width 205 Dia 21 Qty 1	Silver	205	21	1
3	Manufacturer Rubber Tire Width 213 Dia 23 Qty 3	Rubber	213	23	3
Data

Cell Formulas
Range	Formula
B2:E3	B2	=LET(t,MID($A2,FIND(B$1,$A2)+LEN(B$1)+1,99),LEFT(t,FIND(" ",t&" ")))

 

[Special-K99]

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",50)),(2-1)*50+1,50))
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",50)),(5-1)*50+1,50))
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",50)),(7-1)*50+1,50))
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",50)),(9-1)*50+1,50))

 

[silverkay]

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",50)),(2-1)*50+1,50))
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",50)),(5-1)*50+1,50))
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",50)),(7-1)*50+1,50))
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",50)),(9-1)*50+1,50))

Worked perfectly, thank you so much!

 

[Fluff]

Did my suggestion work?

 

[Special-K99]

Worked perfectly, thank you so much!

The number in brackets (2-1), (5-1) etc ie the 2 and 5 etc, indicate the nth word to extract, so the 2nd word, 5th word, 7th word 9th word with space as an indicator

 

[Special-K99]

Did my suggestion work?

I dont have the latest Excel.
Is that function correct LET( or should it be LEFT(

 

[Fluff]

The function is correct & it is meant to be LET, rather than LEFT.

 

[silverkay]

Did my suggestion work?

Hi, apologies for the late reply.

I couldn't figure out how to work around the LET function, and I got #NAME even when I copypasted the formula directly.

 

[Special-K99]

Hi, apologies for the late reply.

I couldn't figure out how to work around the LET function, and I got #NAME even when I copypasted the formula directly.

#NAME indicates that a formula doesn't exist. It must be the LET() function.
Try this, in a blank cell enter

=LET(abc,3+4)

If it says #NAME then you don't have the LET() formula.
I don't have the LET() formula either.