How to remove data from cells where the data is equal to or greater than 9 characters in length?

Question

I need to remove data from cells that is 9 or more digits or characters. For example this should be deleted: 123456789, 987654321, 1234567898765, and so on.

I already got the code that checks every single part of a cell to compare but I have a problem constructing the number specification.

The sample line for code to work on will look like that:

Aegis Transformation Cycle 566609354 Agent 73849496753

My code:

For g = 2 to RowNumber
    MyCell = " " & Cells(g, 2).Value & " "
    Word = Split(MyCell, " ")
    For j = 0 To UBound(Word)
        If Word >= 100000000 Then
            Cells(g, 2).Replace What:=Word(j), Replacement:=""
        End If 
    Next j
Next g

score 1 · Accepted Answer · answered Jan 30 '19 at 14:45

1

One way is using regular expressions.

Sub x()

Dim r As Range

With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "\d{9,}"
    For Each r In Range("A1:A10")
        r.Offset(, 1) = .Replace(r, "")
    Next r
End With

End Sub

answered Jan 30 '19 at 14:45

SJR

22,986
6
18
26

score 1 · Answer 2 · answered Jan 30 '19 at 15:36

You could also use the Len() function with Trim() like so:

Sub DeleteBlanks()

Dim g As Long, RowNumber As Long

    With Sheets("SheetName")
        'Finds the last row in the first column
        RowNumber = .Cells(Rows.Count, 1).End(xlUp).Row

        For g = 1 To RowNumber 
            'Note:Value2 reads cells W/O formatting and is faster
            If Len(Trim(.Cells(g, 1).Value2)) >= 9 Then
                .Cells(g, 1).ClearContents
            End If
        Next g

    End With

End Sub

score 0 · Answer 3 · answered Jan 30 '19 at 14:47

You could achieve this quite easily with Regular Expressions. The following code will identify any part of your string that contains a number equal to or longer than 9 characters and remove them

Public Sub Demo()
    Dim RegExp As Object
    Dim rng As Range
    Dim matches
    Dim c

    Set rng = Sheet1.Range("A1")

    Set RegExp = CreateObject("vbscript.regexp")

    With RegExp
        .MultiLine = False
        .Global = True
        .IgnoreCase = False
        .Pattern = "[0-9]{9,}"

        For Each c In rng
            If .test(c) Then
                Set matches = .Execute(c)
                MsgBox .Replace(c, vbNullString)
            End If
        Next c
    End With

End Sub

How to remove data from cells where the data is equal to or greater than 9 characters in length?

3 Answers3