Extract only numbers from cells with different lengths

[Stuepef]

I have a worksheet with different values within column A and I am looking to extract all of the numbers only. The values in column A can vary in terms of length and characters (ie $, %, .). I understand my current formula is replacing all "-" with " ", but I am looking for assistance to refine it. Open to all suggestions/solutions.

Current formula:

=INT(LEFT(REPLACE(SUBSTITUTE(A6,"-"," "),1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A6&1/17))-1,""),5))

Workbook:

[TABLE="width: 412"]
<tbody>[TR]
[TD][/TD]
[TD]Formula[/TD]
[TD]Desired result[/TD]
[/TR]
[TR]
[TD]Ford $25-95%[/TD]
[TD]#VALUE![/TD]
[TD]25-95[/TD]
[/TR]
[TR]
[TD]Focus $1,000-$30[/TD]
[TD]1000[/TD]
[TD]1000-30[/TD]
[/TR]
[TR]
[TD]Honda $1,000-$45[/TD]
[TD]1000[/TD]
[TD]1000-45[/TD]
[/TR]
[TR]
[TD]LED Bright $2,000-100%[/TD]
[TD]2000[/TD]
[TD]2000-100[/TD]
[/TR]
[TR]
[TD]Best $2,000-70% Quarter back[/TD]
[TD]2000[/TD]
[TD]2000-70[/TD]
[/TR]
[TR]
[TD]HIU Book $2,500-100% Ge Plus[/TD]
[TD]2500[/TD]
[TD]2500-100[/TD]
[/TR]
[TR]
[TD]USA Emboss. green $3,000-80%[/TD]
[TD]3000[/TD]
[TD]3000-80[/TD]
[/TR]
[TR]
[TD]USA Emboss. Green $4,000-100%[/TD]
[TD]4000[/TD]
[TD]4000-100[/TD]
[/TR]
[TR]
[TD]Masters Gold $300[/TD]
[TD]300[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]Masters Gold $750[/TD]
[TD]750[/TD]
[TD]750[/TD]
[/TR]
[TR]
[TD]UK 2500-55-30% Orange[/TD]
[TD]2500[/TD]
[TD]2500-55-30[/TD]
[/TR]
[TR]
[TD]F.ACE.500.80.40[/TD]
[TD]500[/TD]
[TD]500.80.40[/TD]
[/TR]
[TR]
[TD]F.ACE.1000.80.40[/TD]
[TD]1000[/TD]
[TD]1000.80.40[/TD]
[/TR]
[TR]
[TD]F.Ace.1500.60.75[/TD]
[TD]1500[/TD]
[TD]1500.60.75[/TD]
[/TR]
[TR]
[TD]F.ACE.1500.100.25[/TD]
[TD]1500[/TD]
[TD]1500.100.25[/TD]
[/TR]
</tbody>[/TABLE]

I appreciate your help!

 

[sheetspread]

Thank you for the replies! Unfortunately I wont be able to incorporate any VBA into this particular workbook though it would be awesome to do so. There are variable inputs in column A and it wont maintain the same format throughout.

The above code is a function, you can have different versions in different cells. Or change it to a macro/sub that can vary the output patterns.

 

[Stuepef]

I guess I am unfamiliar with how functions work in excel?

 

[sheetspread]

I guess I am unfamiliar with how functions work in excel?

Like a formula, you can write to whichever cells you want. More importantly, you can change the underlying code and choose which of the numbers will be returned.

 

[Stuepef]

this is great, thank you all!

 

[Peter_SSs]

There are variable inputs in column A and it wont maintain the same format throughout.

Not sure just what you mean by that, but another formula suggestion for post #7 data is

Excel Workbook
	A	B
1	Product	Desired Result
2	Pontiac $500-$20	20
3	Feet $500-$40	40
4	USA Feet $2,000-100%	100
5	USA Feet $2,000-100% Gt Plus	100
6	Orange $3,000-70% Treat ACE Free	70
7	USA Emboss. Orange $3,000-80%	80
8	USA Emboss. Orange $4,000-100%	100
9	USA Emboss. Orange $4,000-100% Gt Plus	100
Extract Number