vba 如何用公式替换列的每个现有值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3852628/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to replace every existing values of a column with formula?
提问by Vantomex
My sheet already has data. Unfortunately, later I realized that all the values in column E should be validated and changed as necessary by calling my own user-defined function and passing the values as the function parameter. For example, say I have the following data in column E:
我的工作表已经有数据。不幸的是，后来我意识到 E 列中的所有值都应该根据需要通过调用我自己的用户定义函数并将这些值作为函数参数传递来进行验证和更改。例如，假设我在 E 列中有以下数据：
E1: "This is a text in (E1)"
E2: "This is a text in (E2)"
...
E7000: "This is a text in (E7000)"
Now, I want every value in column E to be changed by a formula like this:
现在，我希望 E 列中的每个值都通过这样的公式进行更改：
E1: = RemoveBrackets("This is a text in (E1)")
E2: = RemoveBrackets("This is a text in (E2)")
...
E7000: = RemoveBrackets("This is a text in (E7000)")
Supposing Excel supports Regex, my problem can be solved easily. but unfortunately Excel doesn't. Could someone propose possible solutions to my problem?
假设 Excel 支持 Regex，我的问题就可以轻松解决。但不幸的是 Excel 没有。有人可以为我的问题提出可能的解决方案吗？
Thanks in advance
提前致谢
采纳答案by Dr. belisarius
1) Insert a new column "F"
1) 插入一个新列“F”
2) Copy column E into F
2) 将 E 列复制到 F
3) Write in E1
3）写在E1
=RemoveBrackets(F1)
4) Drag the E1 value through E7000
4）通过E7000拖动E1值
5) Hide column F.
5) 隐藏 F 列。
Edit 1
编辑 1
You can do it with several passes of the Find/Replace feature:
您可以通过多次查找/替换功能来实现：
Select the Column E before each PASS.
在每个 PASS 之前选择 E 列。
PASS 1
通过 1
Find: <"This is a text in>
Replace with: <RemoveBrackets("This is a text in>
PASS 2
通行证 2
Find: <)">
Replace with: <)")>
PASS 3
第 3 关
Find: <RemoveBrackets(>
Replace with: <=RemoveBrackets(>
Edit 2
编辑 2
VB Regex substitute string function can be used from Excel.
You may dowload a toolpack such as Morefuncwhich supports a REGEX.SUBSTITUTE udf, or you could do your own udf using THISas guideline.
可以从 Excel 中使用 VB 正则表达式替换字符串函数。
您可以下载中心一个toolpack如Morefunc它支持REGEX.SUBSTITUTE UDF，或者你可以用做自己的UDF此为导向。
I am posting here the code from the second reference, just for link independence:
我在这里发布第二个参考中的代码，仅用于链接独立性：
Public Function SearchNReplace1(Pattern1 As String, _
Pattern2 As String, Replacestring As String, _
TestString As String)
Dim reg As New RegExp
reg.IgnoreCase = True
reg.MultiLine = False
reg.Pattern = Pattern1
If reg.Test(TestString) Then
reg.Pattern = Pattern2
SearchNReplace1 = reg.Replace(TestString, ReplaceString)
Else
SearchNReplace1 = TestString
End If
End Function
Please read the full article, as you should turn on the Microsoft VBScript Regular Expressions 5.5 option in Excel first.
请阅读全文，因为您应该首先在 Excel 中打开 Microsoft VBScript 正则表达式 5.5 选项。
HTH
HTH
回答by Jon
Similar to belisarius' method, but doesn't leave a trace:
类似于贝利撒留的方法，但不留痕迹：
- Insert column F
- Insert into F1 the value
=RemoveBrackets(F1) - Copy down through F7000.
- Copy column F
- Right click cell E1, Paste Special, choose "Values".
- Delete column F
- 插入 F 列
- 将值插入 F1
=RemoveBrackets(F1) - 通过 F7000 向下复制。
- 复制 F 列
- 右键单击单元格 E1，选择性粘贴，选择“值”。
- 删除 F 列
