java正则替换excel公式,Microsoft Excel中正则表达式子字符串替换

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?

解决方案

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?