如何用公式替换列中的每个现有值？

发布于 2024-09-26 02:55:59 字数 518 浏览 4 评论 0原文

我的工作表已经有数据了。不幸的是，后来我意识到，E 列中的所有值都应该通过调用我自己的用户定义函数并将这些值作为函数参数传递来进行验证和更改。例如，假设 E 列中有以下数据：

E1: "This is a text in (E1)"
E2: "This is a text in (E2)"
...
E7000: "This is a text in (E7000)"

现在，我希望 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)")

假设 Excel 支持 Regex，我的问题就可以轻松解决。但不幸的是 Excel 没有。有人可以针对我的问题提出可能的解决方案吗？

提前致谢

原文

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:

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:

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?

Thanks in advance

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

子栖 2024-10-03 02:55:59

1) 插入新列“F”

2) 将列 E 复制到 F

3) 写入 E1

 =RemoveBrackets(F1)

4) 将 E1 值拖过 E7000

5) 隐藏列 F。

编辑 1

您可以通过多次查找/替换功能来完成此操作：

在每个 PASS 之前选择 E 列。

PASS 1

Find:              <"This is a text in>
Replace with:      <RemoveBrackets("This is a text in>

PASS 2

Find:              <)">
Replace with:      <)")>

PASS 3

Find:              <RemoveBrackets(>
Replace with:      <=RemoveBrackets(>

编辑 2

VB Regex 替换字符串函数可在 Excel 中使用。
您可以下载支持 REGEX.SUBSTITUTE udf 的工具包，例如 Morefunc，或者您可以使用 THIS 作为指导来制作自己的 udf。

我在这里发布第二个参考中的代码，只是为了链接独立性：

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

请阅读全文，因为您应该首先在 Excel 中打开 Microsoft VBScript 正则表达式 5.5 选项。

华泰

1) Insert a new column "F"

2) Copy column E into F

3) Write in E1

 =RemoveBrackets(F1)

4) Drag the E1 value through E7000

5) Hide column F.

Edit 1

You can do it with several passes of the Find/Replace feature:

Select the Column E before each PASS.

PASS 1

Find:              <"This is a text in>
Replace with:      <RemoveBrackets("This is a text in>

PASS 2

Find:              <)">
Replace with:      <)")>

PASS 3

Find:              <RemoveBrackets(>
Replace with:      <=RemoveBrackets(>

Edit 2

VB Regex substitute string function can be used from Excel.
You may dowload a toolpack such as Morefunc which supports a REGEX.SUBSTITUTE udf, or you could do your own udf using THIS as guideline.

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.

HTH

回复收藏 0 原文