Formula to find any 4 or 5 digit number in a cell

[Maugrahl]

For example: if the cell has the info "SPIN ID 12345 POM ID 12345678", I need to bring the "12345" number. The cell doesn't have an specific format or order (can't use "ID", "SPIN" or "POM" as a reference) but I need to bring the 4 or 5 digit number in the cell. There will be only one number with 4 or 5 digits there.

 

[Scrappy666]

Hey Maugrahl,

Don't ask me how it works, i honestly got lost half way through writing it, but it seems to do the job

=IF(MIN(IF(ISERROR(FIND({1;2;3;4;5;6;7;8;9;0},RIGHT(L9,LEN(L9)-MIN(IF(ISERROR(FIND({1;2;3;4;5;6;7;8;9;0},L9)),"",FIND({1;2;3;4;5;6;7;8;9;0},L9)))-3))),"",FIND({1;2;3;4;5;6;7;8;9;0},RIGHT(L9,LEN(L9)-MIN(IF(ISERROR(FIND({1;2;3;4;5;6;7;8;9;0},L9)),"",FIND({1;2;3;4;5;6;7;8;9;0},L9)))-3))))=1,LEFT(RIGHT(L9,LEN(L9)-MIN(IF(ISERROR(FIND({1;2;3;4;5;6;7;8;9;0},L9)),"",FIND({1;2;3;4;5;6;7;8;9;0},L9)))+1),5),LEFT(RIGHT(L9,LEN(L9)-MIN(IF(ISERROR(FIND({1;2;3;4;5;6;7;8;9;0},L9)),"",FIND({1;2;3;4;5;6;7;8;9;0},L9)))+1),4))

P.S. For some reason I used cell L9 as the example, but change all the L9's to whatever cell you want to look at.

 

[Mackers]

Hi Scrappy

I just tried your formula and it seemed to return up to 5 digits of the first number, rather than the first 4 or 5 digit number, so for example if the cell was "Hello 123 and 45678" it returns "123 " including a space, rather than 45678 as we would wish.

Not sure this will be an issue for Maugrahl but something to bear in mind.

A very hacky way to get around this is to just cut the first, second, third, fourth etc. numbers out of the cell in several columns and then use IF(OR(LEN(A1)=4, LEN(A1)=5), A1, IF(OR(LEN(A2)=4, LEN(A2)=5), A2, IF(OR(LEN(A3)=4, LEN(A3)=5), A3, IF(.... etc)))) to return the appropriate length of cell.

 

[Gary's Student]

Try the following UDF:

Public Function GetNumber(s As String) As String
    Dim v As String
    For i = 1 To Len(s)
        ch = Mid(s, i, 1)
        If IsNumeric(ch) Then
            v = v & ch
        Else
            v = v & " "
        End If
    Next i
    
    v = Application.WorksheetFunction.Trim(v)
    
    ary = Split(v, " ")
    For Each a In ary
        If Len(a) = 4 Or Len(a) = 5 Then
            GetNumber = a
            Exit Function
        End If
    Next a
    
    GetNumber = ""
End Function

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.
If you are using a version of Excel later then 2003, you must save
the file as .xlsm rather than .xlsx

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=GetNumber(A1)

To learn more about macros in general, see:

Getting Started with Macros and User Defined Functions

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

Writing Your Own Functions In VBA

Macros must be enabled for this to work!

 

[Scrappy666]

I did notice, but i was hoping it wouldn't be an issue lol.

It looks like there are faster ways to return the result either way, I still have much to learn!