CODEWITHSUNDEEP
regexexcelvbapattern-matching
sushi
sushi

Reputation: 306

VBA Excel Replace last 2 digits of number if occurs at beginning of string

I'm trying to replace the last two digits of number with "XX BLOCK" if it occurs at the start of the string and has more than 2 digits.

I'm using the Microsoft VBScript Regular Expressions 5.5 reference. 

Dim regEx As New RegExp
With regEx
    .Global = True 'Matches whole string, not just first occurrence
    .IgnoreCase = True 'Matches upper or lowercase
    .MultiLine = True 'Checks each line in case cell has multiple lines
    .pattern = "^(\d{2,})" 'Checks beginning of string for at least 2 digits
End With

'cell is set earlier in code not shown, refers to an Excel cell
regEx.replace(cell.Value, "XX BLOCK")

Desired results:

"1091 foo address"      --> "10XX BLOCK foo address"
"1016 foo 1010 address" --> "10XX BLOCK foo 1010 address"
"foo 1081 address"      --> "foo 1081 address"
"10 bar address"        --> "XX BLOCK bar address"
"8 baz address"         --> "8 baz address"

I'm new to regex and not sure where to start. I tried using ^(\d{2,}) but then it replaces the entire number.

There is also a guarantee that the number (if exists) will always be followed with a white space.

Upvotes: 2

Views: 974

Answers (2)

0m3r
0m3r

Reputation: 12499

https://regex101.com/r/M1QrPZ/1

Pattern = "^\d{2}(\d{2})"

Try the following 

Option Explicit
Private Sub Example()
    Dim RegExp As New RegExp
    Dim Pattern As String
    Dim rng As Range
    Dim Cel As Range

    Set rng = ActiveWorkbook.Sheets("Sheet1" _
                            ).Range("A1", Range("A9999" _
                            ).End(xlUp))


    Dim Matches As Variant
    For Each Cel In rng
        DoEvents
        Pattern = "^\d{2}(\d{2})"

        If Pattern <> "" Then
            With RegExp
                .Global = True
                .MultiLine = True
                .IgnoreCase = False
                .Pattern = Pattern
                 Set Matches = .Execute(Cel.Value)
            End With

            If Matches.Count > 0 Then
                Debug.Print Matches(0) ' full mach
                Debug.Print Matches(0).SubMatches(0) ' sub match
               Cel.Value = Replace(CStr(Cel), Matches(0).SubMatches(0), "XX BLOCK")
            End If
        End If

    Next
End Sub

Upvotes: 2

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627190

You may use

^(\d*)\d{2}\b

Or, if you cannot rely on a word boundary, you may also use

^(\d*)\d{2}(?!\d) ' no digit is allowed after the 2-digit sequence
^(\d*)\d{2}(?!\S) ' a whitespace boundary

And replace with $1XX BLOCK.

See the regex demo.

Details

  • ^ - start of string
  • (\d*) - Group 1: zero or more digits
  • \d{2} - two digits
  • \b - a word boundary, no digit, letter or _ is allowed right after the two digits
  • (?!\d) - a negative lookahead that fails the match if there is a digit immediately to the right of the current location
  • (?!\S) - a negative lookahead that fails the match if there is a non-whitespace char immediately to the right of the current location.

Upvotes: 4

Related Questions