英文:
Extract substring in excel cell
问题
我需要使用函数在Excel单元格中查找并提取子字符串，如果可能的话，不使用VBA。
例如，单元格字符串是"A:Red B:Blue C:Orange"，我想获取B的值，即"Blue"。
英文:
I need to find and extract substring in Excel cell using function, if possible without vba.
e.g. cell string "A:Red B:Blue C:Orange"
and I would like to get value of B which is "Blue"
答案1
得分: 2
使用FILTER和TEXTSPLIT：
=FILTER(SUBSTITUTE(TEXTSPLIT(A1, " "), C1&":", ""), LEFT(TEXTSPLIT(A1, " "), 1)=C1)
英文:
Use FILTER and TEXTSPLIT:
=FILTER(SUBSTITUTE(TEXTSPLIT(A1," "),C1&":",""),LEFT(TEXTSPLIT(A1," "),1)=C1)
答案2
得分: 2
=TEXTBEFORE(TEXTAFTER(A2, B2&":")," ",,,1)
这是输出结果:
TEXTBEFORE的输入参数match_end=1（最后一个输入参数）确保它适用于lkKey列值为C的情况，将字符串的末尾视为分隔符，否则对于此情况将无法工作。不需要在输入的末尾添加额外的空格以使其工作。
英文:
You can try the following:
=TEXTBEFORE(TEXTAFTER(A2,B2&":")," ",,,1)
Here is the output:
The input parameter match_end=1(last input argument) of TEXTBEFORE ensures it works for the lkKey column value C, treating the end of the string as delimiter, otherwise it won't work for this case. No need to add an extra space at the end of the input to make it works.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论