提取以特定字符EXCEL开头的几个单词 [英] Extract several words that start with specific char EXCEL

问题描述

我有这个公式来提取以给定字符"@"开头的特定单词.它工作正常，但是有更多以相同的词开头的单词，它将仅提取第一个单词.

I have this formula to extract a specific word that starts with a given char "@". It is working fine, however, there are more words starting with the same, it will only extract the first word.

如何提取所有内容?

=TRIM(LEFT(SUBSTITUTE(MID(B2,FIND("@",B2),LEN(B2))," ",REPT(" ",100)),100))

以下示例:

Seq Desc                                extracted
1   text @word                          @word
2   text @word_one and @word_two        @word_one

推荐答案

尝试以下公式.这是对您的公式的略微修改.它的作用是，它搜索第一个@符号，然后将其替换为~~并提取包含的单词.然后搜索第二个@符号并执行相同的操作.

Try this formula. it is a slight modification of your formula. What it does is, it searches for the first @ sign than replaces it with ~~ and extracts contained word. then it searches for second @ sign and does the same.

=CONCATENATE(TRIM(LEFT(SUBSTITUTE(MID(B3,FIND("~~",SUBSTITUTE(B3,"@","~~",1)),LEN(B3))," ",REPT(" ",100),1),100)),",",TRIM(LEFT(SUBSTITUTE(MID(B3,FIND("~~",SUBSTITUTE(B3,"@","~~",2)),LEN(B3))," ",REPT(" ",100),1),100)))

如果要添加第三个@单词以提取，只需添加另一个CONCATENATE实例，然后替换此 IFERROR(TRIM(LEFT(SUBSTITUTE(MID(B4，FIND("~~"，~~，SUBSTITUTE(B4，"@"，"~~"，2(对于第三个单词，将值2替换为3))，LEN(B4))部分出现第三次，因此要提取3个值，请使用此公式(我添加了IFERROR部分，以防万一没有找到单词@的情况)

If you would like to add third @ word to extract, just add another instance of CONCATENATE, and replace this IFERROR(TRIM(LEFT(SUBSTITUTE(MID(B4,FIND("~~",SUBSTITUTE(B4,"@","~~",2(for third word replace value 2 with 3)),LEN(B4)) part of the formula with 3rd occurrence. so for extracting 3 values, please use this formula. (I added IFERROR part just in case word @ is not found)

=CONCATENATE(IFERROR(TRIM(LEFT(SUBSTITUTE(MID(B4,FIND("~~",SUBSTITUTE(B4,"@","~~",1)),LEN(B4))," ",REPT(" ",100),1),100)),""),",",IFERROR(TRIM(LEFT(SUBSTITUTE(MID(B4,FIND("~~",SUBSTITUTE(B4,"@","~~",2)),LEN(B4))," ",REPT(" ",100),1),100)),""),",",IFERROR(TRIM(LEFT(SUBSTITUTE(MID(B4,FIND("~~",SUBSTITUTE(B4,"@","~~",3)),LEN(B4))," ",REPT(" ",100),1),100)),""))

这篇关于提取以特定字符EXCEL开头的几个单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！