Extract only some numbers from cell

[djsmarties]

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

 

[barry houdini]

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]

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]

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):

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]

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

 

[barry houdini]

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)

 

[kokkentor]

Sweet, Richard! Your UDF is great!
This board is full of goodies!