从Excel单元内的该字符串中提取子字符串 [英] Extract substring from this string inside Excel cell

问题描述

我有这个Excel单元格A2，其内容为USD.EUR[IDEALPRO,CASH,EUR].

I have this Excel cell A2 with the contents USD.EUR[IDEALPRO,CASH,EUR].

我想使用Excel公式从字符串的最后一部分提取子字符串EUR.我得出的最接近的公式是=RIGHT(A2,4).但是，它返回EUR].

I would like to extract the substring EUR from the last part of the string using an Excel formula. The nearest formula I came out with is =RIGHT(A2,4). However, it returns EUR].

可以使用什么公式来提取子字符串?

What formula can be used to extract the substring?

我正在使用MS Excel 2013.

I am using MS Excel 2013.

推荐答案

如果要搜索的字符串的长度仅为3 bytes，则您的简单公式有效.但是，如果改变了怎么办?请尝试以下

If the string that you are searching is only 3 bytes in length, then your simple formula works. But what if it changes? Try the below,

=MID(SUBSTITUTE(F2,",","#",LEN(F2)-LEN(SUBSTITUTE(F2,",",""))),FIND("#",SUBSTITUTE(F2,",","#",LEN(F2)-LEN(SUBSTITUTE(F2,",",""))))+1,FIND("]",SUBSTITUTE(F2,",","#",LEN(F2)-LEN(SUBSTITUTE(F2,",",""))))-FIND("#",SUBSTITUTE(F2,",","#",LEN(F2)-LEN(SUBSTITUTE(F2,",",""))))-1)

其中F2是您的字符串.此公式将最后一个定界符","和"]"之间的字符串解串.这太复杂了，但可能会对您有所帮助.

Where F2 is your string. This formula unstrings the string between the last delimiter "," and "]". This is too complicated but it might help you.

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