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

 

[GuyP16]

Dante,

All most there. Gets a bug, running Debugger it highlights:
c.Offset(0, 1).Value = IIf(cad <> "", Left(cad, Len(cad) - 2), cad)
Did I goof?

Tnx

 

[DanteAmor]

What does the error message say?
Where do you have your data?
Did you modify the macro?
Did it work for some cells?
Do you know which cell was stopped and what data do you have in that cell?


it will be possible for you to answer each of the questions

 

[GuyP16]

Dante,

The error says :

The data is in column A
I have not modified it.
No, it didn't work for some cells.
Not sure what you mean.

Thanks again!

Guy

 

[Joe4]

I do not know if it just me, but I am not seeing any error message in your reply (note that you cannot just copy and paste images).
Can you type what the message says?

 

[GuyP16]

Run time error '5':

Invalid procedure call or argument

 

[Joe4]

It looks like that happens when it cannot find any 5 digit numbers.
We can avoid that error like this:

Sub Separating_multiple_5_digit()
    Dim c As Range, cad As String
    Dim n As Variant, d As Variant, i As Long
    
    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)))
            If Len(d) = 5 Then cad = cad & d & ", "
        Next
        If cad <> "" Then
            c.Offset(0, 1).Value = IIf(cad <> "", Left(cad, Len(cad) - 2), cad)
        End If
    Next
    MsgBox "End"
End Sub

 

[GuyP16]

Joe,
Thanks for joining the conversation. I have 29k lines of data, and I can categorically state, there are hundreds of 5 digit numbers in the column being looked at. Any other thoughts, or should I add this additional coding anyway?

Thanks!!
Guy

 

[GuyP16]

Joe,

Awesome! So I ran it anyway to see what would happen...It worked to line #17 ,483 and then I got another error, Run-time error #'6': Overflow. What next please?

 

[Joe4]

Do you have any errors in any of the cells?

Let's see what cell is causing issues by adding some error coding:

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)))
            If Len(d) = 5 Then cad = cad & d & ", "
        Next
        'If cad <> "" Then
            c.Offset(0, 1).Value = IIf(cad <> "", Left(cad, Len(cad) - 2), cad)
        'End If
    Next
    
    MsgBox "End"
    Exit Sub
    
err_chk:
    errTitle = "Error in cell " & c.Address(0, 0)
    errMsg = Err.Number & ": " & Err.Description
    MsgBox errMsg, vbOKOnly, errTitle
    
End Sub

Tell us what cell it says is causing the error, and what the value is in that cell.

 

[GuyP16]

Joe,

On your first code set, I ran the debugger and it highlighted:
d = Val(WorksheetFunction.Trim(n(i))). Not sure if that helps?

I then ran your second and got a completely different error:
Error in Cell A2 (which is the first of many blank cells in the data set) 5:Invalid procedure call or argument.

Thanks!

Guy