# Extract Number from a Column with different formats

#### gorommel

##### New Member
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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### JoeMo

##### MrExcel MVP
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
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
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)

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,673
Messages
5,838,705
Members
430,564
Latest member
Raeyven

### 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.

### Which adblocker are you using?

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

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