formula to look for certain key words

[richard hales]

Hi Everyone

I have a formula which as work great for me for a while now =IF(LEN(A2&B2),IF(COUNT(A2,B2)=0,RIGHT(LOOKUP("zzz",A2:B2),2)+0,""),"") can someone please help me to tweak it a bit more.

Am looking for the formula to look for certain key words.

The formula is placed in C2 and looks into A2 for the certain key word bus and then returns the number back to C2 and the same for D2 key word car & E2 key word Bike.

Many thanks
Richard

 

[DeficientOptimism]

For every possible combination in column A will the numbers always appear at the end of the text - as in "10" is at the end of "bus10"?

 

[richard hales]

Hi DeficientOptimism
Thanks you for taking a look.
Yes the numbers would be at the end of the text. Numbers could arrange from 0.5 to double digits like 10,11,12 etc max number would be 22 if that helps.

​

 

[DeficientOptimism]

With all your data you want to extract the key words from, put them in column A.

Then try this in B2.

=LEFT(A2,SEARCH(SUBSTITUTE(A2,LEFT(A2,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),""))-1),""),A2)-1)

 

[richard hales]

123.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1			Bus	Car	Bike
2	Car5		#VALUE!	5	#VALUE!
3
4
5		Cell contains certain text, need to return the numbers after text							Isolate numbers from a text cell
6
7		formula
8
Sheet2

Cell Formulas
Range	Formula
C2	C2	=TRIM(MID(A2,FIND("Bus",A2)+3,99))
D2	D2	=TRIM(MID(A2,FIND("Car",A2)+3,99))
E2	E2	=TRIM(MID(A2,FIND("Bike",A2)+4,99))

Hi DeficientOptimism
Thanks for the formula but it wasn't what I was looking for sorry.
Just found the Xl2bb which you can see.
In there I've have a formula the only thing is when I change the data in cell A2 for example Car5 to Bus5 I get the value error, I would like to have a zero. could anyone help me with the correct formula please.