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

问题描述

在MS Excel中，我想使用一个公式从文本中包含特定字符（=）的单元格中提取单词。

A2：多莉让我做了一个自制的蛋糕和一些松饼。

A3：我们吃了奶酪=蛋糕吃饭

A4：每个人都喜欢面包店如何做一些很棒的蛋糕

A5：约翰尼做了自己的晚餐=晚上，然后清理了厨房

A6：有大量的raing State = Oklahoma

我想从列（A2：A4）中提供以下内容：以下结果列（B2：B4）。

B2：自制=蛋糕

B3：cheese = cake

B4：awesome = cakes

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屋！