Menu
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?
826
Because the replacement pattern is $ {amount}, the call to the Regex.Replace method replaces the entire matched substring with this captured group. The $$ substitution inserts a literal "$" character in the replaced string.
Substitutions in Regular Expressions. Substitutions are language elements that are recognized only within replacement patterns. They use a regular expression pattern to define all or part of the text that is to replace matched text in the input string. The replacement pattern can consist of one or more substitutions along with literal characters.
Are there Regex functions in Excel? What is regular expression? A regular expression (aka regex or regexp) is a specially encoded sequence of characters that defines a search pattern. Using that pattern, you can find a matching character combination in a string or validate data input.
Because Excel's standard features can only process an exact string that you specify. To find a string that matches some pattern and replace it with something else, regular expressions are indispensable. As it's generally known, built-in Excel functions do not support regular expressions.
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?
118
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
