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]

Better to use regex in VBA, but a formula is possible:

Excel 2010
	A	B	C
1	Ford $25-95%	25-95	25-95
2	Focus $1,000-$30	1000-30	1,000-30
3	Honda $1,000-$45	1000-45	1,000-45
4	LED Bright $2,000-100%	2000-100	2,000-100
5	Best $2,000-70% Quarter back	2000-70	2,000-70
6	HIU Book $2,500-100% Ge Plus	2500-100	2,500-100
7	USA Emboss. green $3,000-80%	3000-80	3,000-80
8	USA Emboss. Green $4,000-100%	4000-100	4,000-100
9	Masters Gold $300	300	300
10	Masters Gold $750	750	750
11	UK 2500-55-30% Orange	2500-55-30	2500-55-30
12	F.ACE.500.80.40	500.80.40	500.80.40
13	F.ACE.1000.80.40	1000.80.40	1000.80.40
14	F.Ace.1500.60.75	1500.60.75	1500.60.75
Sheet3

Cell Formulas
Range	Formula
C1	{=SUBSTITUTE(MID(A1,MATCH(TRUE,ISNUMBER(0+MID(A1,ROW(INDIRECT(1&":"&LEN(A1))),1)),0),1+LOOKUP(2,1/ISNUMBER(0+MID(A1,ROW(INDIRECT(1&":"&LEN(A1))),1)),ROW(INDIRECT(1&":"&LEN(A1))))-MATCH(TRUE,ISNUMBER(0+MID(A1,ROW(INDIRECT(1&":"&LEN(A1))),1)),0)),"$","")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Rick Rothstein]

This normally-entered, non-volatile formula is rather long (a tad shorter than sheetspread's formula though), but it seems to do what you want...

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),FIND(" ",A1&" ",MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")))-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))),"$",""),"%",""),",","")

 

[Stuepef]

Thank you both so much, it works like a charm! Sending Rep!!

 

[bosco_yip]

Another shorter option :

=SUBSTITUTE(SUBSTITUTE(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1/17)),FIND("%",A1&"%")-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1/17))),"$",""),",","")

Regards
Bosco

 

[Rick Rothstein]

Another shorter option :

=SUBSTITUTE(SUBSTITUTE(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1/17)),FIND("%",A1&"%")-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1/17))),"$",""),",","")

That will work as long as none of the non-percentage numbers do not have text following them. In other words, it won't work for something like this...

F.Ace.1500.60.75 Orange

Admittedly, none of the OP's examples showed a value like this, and I don't know if such a combination is even possible, but I thought it could possibly be which is why I posted the formula that I did.

 

[Stuepef]

@Rick Rothstein - How would I update the formula to show these results:

[TABLE="width: 387"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Desired Result[/TD]
[/TR]
[TR]
[TD]Pontiac $500-$20[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Feet $500-$40[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]USA Feet $2,000-100%[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]USA Feet $2,000-100% Gt Plus[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Orange $3,000-70% Treat ACE Free[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD]USA Emboss. Orange $3,000-80%[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]USA Emboss. Orange $4,000-100%[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]USA Emboss. Orange $4,000-100% Gt Plus[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

As suggested by sheetspread, you could consider using user-defined functions like these. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function GetNums(s As String) As String
  Static RX As Object
  
  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
  End If
  RX.Pattern = "(\D*)([\d\$\-\.,]+)(\D*)"
  GetNums = Replace(Replace(RX.Replace(s, "$2"), "$", ""), ",", "")
End Function


Function LastNum(s As String) As Long
  Static RX As Object
  
  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
  End If
  RX.Pattern = "[\d,]+(?=\D*$)"
  LastNum = RX.Execute(s)(0)
End Function

Excel Workbook
	A	B	C
2	Ford $25-95%	25-95	95
3	Focus $1,000-$30	1000-30	30
4	Honda $1,000-$45	1000-45	45
5	LED Bright $2,000-100%	2000-100	100
6	Best $2,000-70% Quarter back	2000-70	70
7	HIU Book $2,500-100% Ge Plus	2500-100	100
8	USA Emboss. green $3,000-80%	3000-80	80
9	USA Emboss. Green $4,000-100%	4000-100	100
10	Masters Gold $300	300	300
11	Masters Gold $750	750	750
12	UK 2500-55-30% Orange	2500-55-30	30
13	F.ACE.500.80.40	500.80.40	40
14	F.ACE.1000.80.40	1000.80.40	40
15	F.Ace.1500.60.75	1500.60.75	75
16	F.ACE.1500.100.25	1500.100.25	25
Extract numbers

 

[sheetspread]

A code like Peter's is the most flexible for variable criteria. Your particular sample in Post 7 can be solved with this formula:

Excel 2010
	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
Sheet6

Cell Formulas
Range	Formula
B2	=SUBSTITUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2,"-",REPT(" ",100)),"%",REPT(" ",100)),100,100)),"$","")

But it doesn't really handle exceptions.

 

[Stuepef]

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.