Regular expression substring replacement in Microsoft Excel [closed]

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

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?

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)