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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,392
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

jgrijalba

New Member
Joined
Oct 14, 2014
Messages
3
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,722
Office Version
  1. 2010
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,525
Messages
5,602,173
Members
414,509
Latest member
Cdavis7078

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
Top