Deleting cell contents starting with numbers VBA

I need a way to loop through cells in a specific column (eg. A) and check if they start with 5 or more numbers. If so I need to delete everything after the numbers end.

So for example it will start like this

4165460 ABC

546445 SDF

4654843354 FHD

ABD 132

1254 AHD

And need it to end up like this

4165460

546445

4654843354

ABD 132

1254 AHD

Try:

Sub b1358354c()
Dim i As Long
Dim n As String
Dim va

va = Range("A1", Cells(Rows.Count, "A").End(xlUp))
For i = 1 To UBound(va, 1)
    n = Trim(Val(va(i, 1)))
    If Len(n) > 4 Then va(i, 1) = n
Next
Range("B1").Resize(UBound(va, 1), 1) = va

End Sub

Originally Posted by sjk1193

I need a way to loop through cells in a specific column (eg. A) and check if they start with 5 or more numbers.

Is it ever possible that one or more of your 5+ digit numbers will start with a zero? If so, do you need to keep that leading zero?

It may happen, just looking for the most robust solution, thanks

Originally Posted by sjk1193

...just looking for the most robust solution

This macro will do what you want even if there is no space between the leading number and the text following it and it preserve leading zeros too...

Sub FiveDigitOrMore()
  Dim R As Long, X As Long, Data As Variant
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  For R = 1 To UBound(Data)
    If Data(R, 1) Like "#####*" Then
      For X = 6 To Len(Data(R, 1))
        If Mid(Data(R, 1), X, 1) Like "[!0-9]" Then Data(R, 1) = Left(Data(R, 1), X - 1)
      Next
    End If
  Next
  With Range("B1").Resize(UBound(Data))
    .NumberFormat = "@"
    .Value = Data
  End With
End Sub