Extract only some numbers from cell

djsmarties

Board Regular
Joined
Sep 10, 2002
Messages
95
Hi,

I know there are many post on similar topics already, but I cannot seem to find one that will solve my problem

I have a table with a column that contain text/number mix in this format:

text123text1234text

Sometimes there are spaces, = signs, or commas separating the text from the numbers.

I found a formula that extracts numbers but it returns the first set of number, eg. 123. What I want however, is to extract the second set of numbers, e.g. 1234. The number is always four digits long, if that makes it any easier.


Thanks a lot
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
If it's always the last 4 digit number try

=MID(A1,MATCH(2,1/MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))-3,4)

confirmed with CTRL+SHIFT+ENTER

or perhaps better...

=LOOKUP(10000,MID(SUBSTITUTE(A1,"=","x"),ROW(INDIRECT("1:"&LEN(A1))),4)+0)

only requires enter
 

djsmarties

Board Regular
Joined
Sep 10, 2002
Messages
95
I tried the second one and it worked, but not quite. I get only the last 3 digits in some cases, only 1 in some cases.

The problem is that the source string is not always in the same format, that is sometimes there is some text at the beginnning, sometimes not. But the number i want to extract is always 4-digit. Is there anyway to have formula that would find any four digit number in a cell and return that?
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi

If it's the only instance of 4 sequential numbers (so ABC1234UYT6789hgff would not work as there is a preceding 4digit numeric string) then you can give the following UDF a try (paste into a standard module in your workbook):

Code:
Function NumOnly(txt As String) As String
Dim regex As Object, currMatches
Set regex = CreateObject("VBScript.RegExp")
With regex
    .Pattern = "\d\d\d\d"
    .Global = True
End With
Set currMatches = regex.Execute(txt)
NumOnly = ""
If currMatches.Count > 0 Then
    NumOnly = currMatches(0)
End If
End Function

Richard
 

djsmarties

Board Regular
Joined
Sep 10, 2002
Messages
95

ADVERTISEMENT

This works exactly the way I want it to work :). Thanks a lot :)
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
I tried the second one and it worked, but not quite. I get only the last 3 digits in some cases, only 1 in some cases.

Looks like Richard gave you a good VBA solution. If you're still interested in a formula solution then....

my original proposal would fall down if you have spaces immediately after the 4 digit number - this amendment should fix that

=LOOKUP(10000,MID(SUBSTITUTE(SUBSTITUTE(A1,"=","x")," ","x"),ROW(INDIRECT("1:"&LEN(A1))),4)+0)
 

Forum statistics

Threads
1,144,278
Messages
5,723,465
Members
422,498
Latest member
KAT112014

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