Filtering Numbers out of a Text String

[Watson221]

Hi Everyone,
I'm having some trouble extracting numbers from a string of text.
I need to get the information next to HP, so I thought I could use
=MID(D8,SEARCH({0,1,2,3,4,5,6,7,8,9},D8)-1, SEARCH("HP",D8)+2)

But It doesn't scale well as HP's position is different in different cells.

From this information I'd like to extract the number 30
30HP 1800RPM 460/3/60 286 TSC H135-80539-0

From this info 15
MOTOR:AC;254T;15HP;3PH;460V;60HZ;1750RPM

From this info 100
PARTS KIT,CENT FAN,100 HP DRV,CLG

So I'm wondering if there's a way to ask Search to look for non-number values or an all together better way to accomplish this.
Thanks!

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff New.xlsm
	A	B
1
2	30HP 1800RPM 460/3/60 286 TSC H135-80539-0	30
3	MOTOR:AC;254T;15HP;3PH;460V;60HZ;1750RPM	15
4	PARTS KIT,CENT FAN,100 HP DRV,CLG	100
Admin

Cell Formulas
Range	Formula
B2:B4	B2	=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(LEFT(A2,SEARCH("hp",A2)-1),";",","),",",REPT(" ",100)),100))

 

[Watson221]

Wow Thanks! That scaled nicely to about 95% of the cells . Thanks, you saved me a ton of work.
Would you find breaking it down a bit and explaining why it works?
Thanks you so much!

 

[Fluff]

Can you post some examples of those that didn't work?

 

[Watson221]

Yeah no problem

An example would be this
Motor Toshiba 20HP,1750 RPM,460V,3PHASE,60HZ TEFC,Foot Mount,256T Frame Y 556.759
Would output as
Motor Toshiba 20

INPUT
FAN COIL UNIT - 40HP MOTOR
Output
FAN COIL UNIT - 40

 

[Fluff]

Ok, how about
=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(LEFT(A2,SEARCH("hp",A2)-1)),";",",")," ",","),",",REPT(" ",100)),100))

 

[Watson221]

Awesome thanks! That got it up to 99%! Only had to manually input around 10 out out of 2000! Thanks a ton!

 

[Fluff]

You're welcome & thanks for the feedback.