Separating one or multiple 5 digit numbers in a text string.

[GuyP16]

Hi All,

New here, first posting. Kinda fun.

I am working with some free text data that includes one of multiple five digit numbers I need to separate out. I have found an equation from here that pulled one code, but not all. Could be up to 8 in one free text set. I am not good with VBA, so would request help running a macro to please.

Thanks in advance,

Guy P

 

[Joe4]

Try this (note the amended code in red):

Sub Separating_multiple_5_digit()

    Dim c As Range, cad As String
    Dim n As Variant, d As Variant, i As Long
    Dim errTitle As String, errMsg As String
    
    On Error GoTo err_chk
    For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
        n = Split(Replace(c.Value, ",", " "), " ")
        cad = ""
        For i = 0 To UBound(n)
            d = Val(WorksheetFunction.Trim(n(i)))
[COLOR=#ff0000]            If Len(d) = 5 Then
'               Only add new value to string if not already found in string
                If InStr(1, cad, d) = 0 Then
                    cad = cad & d & ", "
                End If
            End If[/COLOR]
        Next
        If cad <> "" Then
            c.Offset(0, 1).Value = IIf(cad <> "", Left(cad, Len(cad) - 2), cad)
        End If
    Next
    
    MsgBox "Finished"
    Exit Sub
    
err_chk:
    Select Case Err.Number
        Case 5, 6
            'ignore error numbers 5 and 6
            Err.Clear
            Resume Next
        Case Else
            'Other errors
            errTitle = "Error in cell " & c.Address(0, 0)
            errMsg = Err.Number & ": " & Err.Description
            MsgBox errMsg, vbOKOnly, errTitle
    End Select
    
End Sub

 

[GuyP16]

Joe,

Just ran it. Looks like a go. Thanks again!

Guy

 

[Joe4]

You are welcome.