Finding all four digit numbers in a string

jjsande1492 · Jul 12th 2012

Hi all. I was hoping the collective brain power in here is better than mine right now.

I have a column of strings. In each string there will be anywhere from one to three four-digit numbers separated by something, but it is not always the same thing. The numbers are also not in the same place.
For example:

A1: blah blah 1234 blah 5678
A2: blah 9876
A3: blah blah blah..1234 blah
A4: blah, 4554 9977

Does anyone have any suggestions on how to pull out the four digit numbers? VBA is preferable.

Cheers

jindon · Jul 12th 2012

Re: Finding all four digit numbers in a string

Code

Sub test()
Dim r As Range, i As Long
With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "\b\d{4}\b"
    For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
        If .test(r.Value) Then
            For i = 0 To .Execute(r.Value).Count - 1
                r(, i + 2).Value = .Execute(r.Value)(i)
            Next
        End If
    Next
End With
End Sub

Display More

jjsande1492 · Jul 12th 2012

Re: Finding all four digit numbers in a string

Simply awesome. Works like a charm. Thanks, Jindon.

Regular expressions is not something I've had a chance to learn yet and to be honest, they intimidate me (though I'm sure I'm overreacting).

**Trebor76** · Jul 12th 2012

Re: Finding all four digit numbers in a string

Here's another way that puts the results into a blank column:

Code

Option Explicit


Sub Macro2()
    
    'http://www.ozgrid.com/forum/showthread.php?t=167546
    
    'Lists out the numeric values from an alphanumeric string contained in column A into the first avaliable (blank) column of the active sheet.
    
    Dim lngMyCol As Long, _
        lngMyRow As Long
    
    Application.ScreenUpdating = False
    
    lngMyCol = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
    lngMyRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    Cells(1, lngMyCol).FormulaArray = "=SUM(MID(0&A1,LARGE(ISNUMBER(--MID(A1,ROW(INDIRECT(""1:""&LEN(A1))),1))* ROW(INDIRECT(""1:""&LEN(A1))),ROW(INDIRECT(""1:""&LEN(A1))))+1,1)*10^ROW(INDIRECT(""1:""&LEN(A1)))/10)"
    Cells(1, lngMyCol).Copy Range(Cells(2, lngMyCol), Cells(lngMyRow, lngMyCol))
    
    With Columns(lngMyCol)
        .Calculate 'Ensure the formulas have calculate prior to making them a value
        .Value = .Value 'Convert the above array formula to values
    End With
        
    Application.ScreenUpdating = True


End Sub

Display More

HTH

Robert

Atif Bhatti · Oct 7th 2021

Quote from jindon
Re: Finding all four digit numbers in a string
Code
Sub test()
Dim r As Range, i As Long
With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "\b\d{4}\b"
    For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
        If .test(r.Value) Then
            For i = 0 To .Execute(r.Value).Count - 1
                r(, i + 2).Value = .Execute(r.Value)(i)
            Next
        End If
    Next
End With
End Sub
Display More
You are awesome, jindon you are fantastic dear, it workded fine for 4-digit numeric values. You made my day.
Now can you please help me by making two smiliar VBA codes one for 4 digit Alpha value and second for four digit Alpha+Numeric mix value. Thanks in advance.

**royUK** · Oct 8th 2021

Welcome to the Forum

Please start your own post. Posting in another member's Thread is known as hijacking and is not allowed here. By all means add a link to a Thread that may be related to your question.

Finding all four digit numbers in a string

Participate now!

Share