Extract only numbers from a cell with text and numbers....

[jamiguel77]

hi all
i have in


A1 = 'LITROS DE LECHE 12,454.00 DE PROV1'
A2 = 'LITROS DE LECHE 274,354.20 DE PROV DE KANSAS'
A3 = 'KILOGRAMOS DE GRASA 1,612,564.00 DE PROVEEDOR DE JIMENEZ'
A4 = 'LITROS DE LECHE 429,573.00 DE TORREON'

with any formula i want extract only the numbers (isnt important if can get the ,)
B1 = 12,454.00
B2 = 274,354.20
B3 = 1,612,564.00
B4 = 429,573.00


any ideas? or only with VBA?

Thanks

 

[Roderick_E]

Can you use a VBA solution?
Ctrl + F11
paste this in blank space

Function removeAlpha(r As String) As String
With CreateObject("vbscript.regexp")
    .Pattern = "\D"
    .Global = True
    removeAlpha = .Replace(r, "")
End With
End Function

Then in B1 type =removealpha(A1) and drag down

 

[jtakw]

Hi,

If there's Always Only One number string within the data string, here's a formula you can use:

Book1
	A	B	C
1	LITROS DE LECHE 12,454.00 DE PROV1	12,454.00	12454
2	LITROS DE LECHE 274,354.20 DE PROV DE KANSAS	274,354.20	274354.2
3	KILOGRAMOS DE GRASA 1,612,564.00 DE PROVEEDOR DE JIMENEZ	1,612,564.00	1612564
4	LITROS DE LECHE 429,573.00 DE TORREON	429,573.00	429573
Sheet59

Cell Formulas
Range	Formula
B1	=TRIM(LEFT(SUBSTITUTE(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),255)," ",REPT(" ",100)),100))
C1	=LEFT(SUBSTITUTE(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),255)," ",REPT(" ",100)),100)+0

Formulas copied down.

B1 formula results in TEXT, shows Exactly as within original string.
C1 formula converts results to Real NUMBERS, format cell to Number in format you want (i.e. with comma and/or 2 decimals)

 

[jamiguel77]

jtakw
Worked Fine! thanks


Roderick_E

the vba code return text cant format as a number.. how to fix

Thanks