Extract Number from a Column with different formats

[gorommel]

Hello.

I have a data set that looks like this. What formula can I use to pull just the numbers?

AIP FEES 26874695 AIP FEES 27075200 A/C 21396262 SAT A/C 21840656 PRTL <tbody> </tbody> I want: 26874695 27075200 21396262 21840656 <tbody> </tbody>

<tbody>
</tbody>

thank you in advance for you help

 

[JoeMo]

Assuming there is only a single number per cell, here's a UDF you can use:

Excel Workbook
	A	B
3	AIP FEES 26874695	26874695
4	AIP FEES 27075200	27075200
5	A/C 21396262 SAT	21396262
6	A/C 21840656 PRTL	21840656
ExtractOnlyNymbers

Function ExtractOnlyNumbers(ByVal S As String) As Variant
Dim Patt As Variant
Patt = "[^0-9]"
If S = "" Then
    ExtractOnlyNumbers = ""
    Exit Function
End If
With CreateObject("VBScript.regexp")
    .Global = True
    .Pattern = Patt
    If .test(S) Then
        ExtractOnlyNumbers = Val(.Replace(S, ""))
    Else
        ExtractOnlyNumbers = Val(S)
    End If
End With
End Function

 

[jgrijalba]

Hi,

You can try this formula. only works in Excel 2010 and up since the aggregate function was not available back then. Also assuming that all the numbers are 9 characters long.

=MID(E20,AGGREGATE(15,6,SEARCH({0,1,2,3,4,5,6,7,8,9},E20),1),9)

E20 is the cell containing the original data..

 

[Rick Rothstein]

It looks like all your numbers are 8 digits long. If that is the case, and if there are no other numbers located before it in the cell, then give this formula a try...

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