Microsoft Excel中正则表达式子字符串替换 [英] Regular expression substring replacement in Microsoft Excel

问题描述

我想用国际版447批量替换字符串列表（手机号码）的07部分。

字符串目前在Excel电子表格中形成列。

我有正则表达式来匹配需要修改的字符串：

  ^ 07 [0-9] {9} $ 
  

...但我不知道如何做我需要的替换。

数据在Excel电子表格中，但当然可以导出。 p>

首选解决方案是将数据保留在Microsoft Excel中，但当然可以导出然后重新导入。我知道TextMate有一个正则表达式替换功能。这可以帮助我吗？

解决方案

我即将去寻找优雅的VBA解决方案或其他，然后我想：上。我们只想在我们拥有的电子表格中操纵一些数据。为什么复杂的事情？

这个想法对你有什​​么看法：

• 使用以下公式填充新列：

  使用现有数据在列之后插入一个新列（我们假设是C列） code> =447& RIGHT（C1，9）




• 选择列D（现在包含新值）和粘贴值粘贴特殊对话框）到列C上，替换现有值




• 删除'working'列D







这不是编程，但是如果你只需要一个程序就可以了，对吗？




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?

这篇关于Microsoft Excel中正则表达式子字符串替换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！