Excel 文本公式提取字段中以分号分隔的单词

发布于 2024-08-21 10:19:43 字数 532 浏览 10 评论 0原文

Excel 中的字段包含用分号分隔的单词，例如：

A1 = 保存；国家；宝藏；为；善

如何应用 Excel 文本公式在另一个字段中从该字段生成单独的单词？例如：

A2 should contain a formula to get the first word ("save")
A3 should contain a (different) formula to get the second word ("the")
etc.

然而，即使 A1 中的值发生变化，这些公式也应该保持有效，例如，如果 A1 的值更改为

A1 = 你好；那里；怎么样；是；你

在这方面的任何帮助将不胜感激。

（问题是在这种情况下不允许编写我自己的函数，我必须使用原始函数，例如 find、search、mid ， ETC。）

原文

A field in Excel contains words separated by semicolons, e.g.:

A1 = save;the;national;treasure;for;good

How can I apply Excel text formulas to produce separate words from this field in another fields? E.g.:

A2 should contain a formula to get the first word ("save")
A3 should contain a (different) formula to get the second word ("the")
etc.

However these formulas should hold good even when the value in A1 changes, e.g. if the value of A1 is changed to

A1 = hello;there;how;are;you

Any help in this respect will be highly appreciated.

(The problem is writing a function of my own is not allowed in this case, I have to use original functions like find, search, mid, etc.)

分享到QQ

分享到微博

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

发布评论

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

段念尘 2024-08-28 10:19:43

您可以创建一个 VBA 函数来拆分此示例中的字段：

Function ExtractElement(str, n, sepChar)
'   Returns the nth element from a string,
'   using a specified separator character
    Dim x As Variant
    x = Split(str, sepChar)
    If n > 0 And n - 1 <= UBound(x) Then
       ExtractElement = x(n - 1)
    Else
        ExtractElement = ""
    End If
End Function

然后是 A2 公式将是：=ExtractElement(A1, 1, ";") 和 A3 将是：=ExtractElement(A1, 2, ";") 等等

You can create a VBA function to split the fields from this example:

Function ExtractElement(str, n, sepChar)
'   Returns the nth element from a string,
'   using a specified separator character
    Dim x As Variant
    x = Split(str, sepChar)
    If n > 0 And n - 1 <= UBound(x) Then
       ExtractElement = x(n - 1)
    Else
        ExtractElement = ""
    End If
End Function

Then the A2 formula would be: =ExtractElement(A1, 1, ";") and A3 would be: =ExtractElement(A1, 2, ";") and so on

回复收藏 0 原文

赏烟花じ飞满天 2024-08-28 10:19:43

如果您要在 A1 中解析文本，则以下公式应该有效

在 A2 中输入公式

=IF(ISERROR(LEFT(A1,FIND(";",A1)-1)),A1,LEFT(A1,FIND(";",A1)-1))

在 B2 中输入公式

=IF(ISERROR(RIGHT(A1,LEN(A1)-FIND(";",A1))),"",RIGHT(A1,LEN(A1)-FIND(";",A1)))

您可以根据需要将它们复制下来。 A 列抓取最左边的单词，B 列显示要解析的剩余字符串。如果没有足够的单词来解析，则公式将显示空白。 B 列也可以隐藏。

If you have your text to parse in A1 then the following formulas should work

In A2 enter the formula

=IF(ISERROR(LEFT(A1,FIND(";",A1)-1)),A1,LEFT(A1,FIND(";",A1)-1))

In B2 enter the formula

=IF(ISERROR(RIGHT(A1,LEN(A1)-FIND(";",A1))),"",RIGHT(A1,LEN(A1)-FIND(";",A1)))

You can then copy those down as far as you need. Column A grabs the left most word, and Column B displays the remaining string to be parsed. If it runs out of words to parse the formula will display a blank. Column B can also be hidden.

回复收藏 0 原文