Regular expression substring replacement in Microsoft Excel

Question

I would like to bulk replace the "07" part of a list of strings (mobile telephone numbers) with the international version "447".

The list of strings currently forms a columnn in an Excel spreadsheet.

I have the regular expression to match strings requiring modification:

^07[0-9]{9}$

...but I don't know how to do the replacement that I need.

The data is in an Excel spreadsheet, but can of course be exported.

Preferred solution would be to keep the data in Microsoft Excel, but it can of course be exported and then re-imported. I know TextMate has a regular expression replace feature. Can this help me?

Answer 1

You'll have to include Microsoft Regular Expressions in your sheet (add it as a Reference)

Then make a quick macro, like the following, to use it:

Dim reg As New RegExp
Public Function RegMatch(Source As Range, Pattern As String, Optional IgnoreCase As Boolean = True, Optional MultiLine As Boolean = True) As Long
    Dim rng As Range, i As Long, j As Long

    reg.IgnoreCase = IgnoreCase
    reg.MultiLine = MultiLine
    reg.Pattern = Pattern

    i = 0: j = 0
    For Each rng In Source
        i = i + 1
        If reg.test(rng.Value) Then
            j = i
            Exit For
        End If
    Next
    RegMatch = j
End Function

Then, simply call it as a macro in your sheet (for example):

=INDEX(B6:B15, RegMatch($A$6:$A$15, $A$3))

Where the first argument is your range, and the second argument is your pattern (as above)

Answer 2

Use Excel VBA. Make a reference to "Microsoft VBScript Regular Expressions 5.5".

Then do, in a new regular VBA module:

Sub ReplaceMobileNumbers
  Dim re as New RegExp

  re.Pattern = "^0(?=7[0-9]{9}$)"   ''# look-ahead

  Dim cell As Range
  For Each cell In ActiveSheet.Range("Your Range Address in A1:B1 notation")
   cell.Value = re.Replace(cell.value, "44")
  Next cell
End Sub

and call this sub in the Immediate Window. The above is throw-away code, not designed with re-usability in mind. I know that, so don't tell me. ;-)

Though you can probably get away with a cell function:

=IF(AND(LEN(A1) = 11;LEFT(A1; 2) = "07"); "44" & RIGHT(A1; 10); A1)

Answer 3

I was about to go off looking for elegant VBA solutions or whatever, then I thought: 'Hang on. We just want to manipulate some data in a spreadsheet we own. Why complicate things?'

How does this idea sound to you:

• insert a new column just after the column with the existing data (let's assume that is column C)

• fill the new column with this formula: ="447" & RIGHT(C1, 9)

• select column D (which now contains the new values) and Paste Values (which is in the Paste Special dialog) onto column C, replacing existing values

• delete the 'working' column D

It's not programming but if you only have to do it once you don't need a program, right?