Extract Number from a Column with different formats

gorommel

New Member
Joined
Jun 25, 2015
Messages
1
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Assuming there is only a single number per cell, here's a UDF you can use:
Excel Workbook
AB
3AIP FEES 2687469526874695
4AIP FEES 2707520027075200
5A/C 21396262 SAT21396262
6A/C 21840656 PRTL21840656
ExtractOnlyNymbers


Code:
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
 
Upvote 0
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..
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top