从包含子字符串中特定字符的字符串中提取单词 [英] Extract a Word from String Containing a Specific Character within Substring

问题描述

在 MS Excel 中，我想使用公式仅从文本中包含特定字符 ("=") 的单元格中提取单词.

A2:多莉给我做了一个自制的蛋糕和一些松饼

A3:我们晚餐吃了奶酪=蛋糕

A4:每个人都喜欢面包店如何制作一些很棒的蛋糕

A5:约翰尼昨晚自己做了晚餐，然后打扫了厨房

A6:有大量降雨 State=Oklahoma

我希望列 (A2:A4) 中的以下内容在列 (B2:B4) 中提供以下结果.

B2:自制=蛋糕

B3:奶酪=蛋糕

B4:很棒=蛋糕

B5:晚餐=昨晚

B6:州=俄克拉荷马州

我尝试了几种方法，有些方法比其他方法更接近，但如果可能的话，还是无法弄清楚.

解决方案

使用旧的文本解析技巧，通过

B2 中的公式是，

=TRIM(MID(SUBSTITUTE(A2, " ", REPT(" ", 99)), MAX(1, FIND("=", SUBSTITUTE(A2, " ", REPT(" ", 99))))-50), 99))

TRIM 功能(用作包装器)删除前导和尾随空格.

In MS Excel I would like to use a formula to extract only the word from a cell that contains a specific character ("=") within the text.

A2: Dolly made me a homemade=cake and some muffins

A3: we had cheese=cake for dinner

A4: Everyone loves how the bakery makes some awesome=cakes

A5: Johnny made his own dinner=lastnight and then cleaned the kitchen

A6: There was a tremendous amount of raing State=Oklahoma

I would like the following from in column (A2:A4) to provide the following results in column (B2:B4).

B2: homemade=cake

B3: cheese=cake

B4: awesome=cakes

B5: dinner=lastnight

B6: State=Oklahoma

I've attempted several approaches, some closer than other, but not able to figure it out, if at all possible.

解决方案

Use an old text parsing trick that greatly increases the distance between the words with repeating zeroes through the SUBSTITUTE and REPT functions which affords a larger swipe of the intended substring.

      

The formula in B2 is,

=TRIM(MID(SUBSTITUTE(A2, " ", REPT(" ", 99)), MAX(1, FIND("=", SUBSTITUTE(A2, " ", REPT(" ", 99)))-50), 99))

The TRIM function (used as a wrapper) removes leading and trailing spaces.

这篇关于从包含子字符串中特定字符的字符串中提取单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！