Removing Extraneous Numbers w/ Specific Format

[aje35]

I have a column of names that I pass through a macro. The names get concatenated with an ID number in some cases. e.g. "Campbell" becomes "0131 Campbell" and if it passes through again it can become "4438 0131 Campbell". The number is always 4 digits and there is always a space. I was looking for some way to code in VBA an IF statement that would look for a #### followed by a space and if this formatting exists (or exists in multiple iterations) then it would remove those characters.

Any help would be appreciated.

Cheers,

AJ

 

[AlphaFrog]

Try something like this...

For Each cell In Range("A1", Range("A" & Rows.Count).End(xlUp))
    If cell.Value Like "#### *" Then cell.Value = Mid(cell.Value, 6)
Next cell

 

[aje35]

That works perfectly in changning "0004 Goudon" to "Goudon"!

Is there a reason that the following wouldn't work for changing "0005 4885 Goudon" to "Goudon"? Also do you think there is any more optimal way to code this to decrease run time?

For Each cell In Range("A1", Range("A" & Rows.Count).End(xlUp))
    If cell.Value Like "#### *" Then cell.Value = Mid(cell.Value, 6)
    If cell.Value Like "#### #### *" Then cell.Value = Mid(cell.Value, 11)
Next cell

Thank you for your help.

 

[AlphaFrog]

This will remove multiples e.g.; 1234 5678 9876 Campbell will become Campbell

Sub Macro1()

    Dim rng As Range, vdata As Variant, i As Long

    Set rng = Range("A1", Range("A" & Rows.Count).End(xlUp))
    vdata = rng

    For i = LBound(vdata, 1) To UBound(vdata, 1)
        If vdata(i, 1) Like "#### *" Then vdata(i, 1) = Strip(vdata(i, 1))
    Next i
    
    rng = vdata

End Sub

Function Strip(ByVal str As String) As String
    str = Mid(str, 6)
    If str Like "#### *" Then str = Strip(str)
    Strip = str
End Function

 

[aje35]

Works like a charm.