Extract substring from this string inside Excel ce

问题:

I have this Excel cell A2 with the contents USD.EUR[IDEALPRO,CASH,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?

I am using MS Excel 2013.

回答1:

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)

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

回答2:

I will answer my own question. It has been tested to work.

=SUBSTITUTE(RIGHT(A2,4),"]","")

RIGHT(A2,4) returns EUR]. Afterwards, use SUBSTITUTE to remove ].

回答3:

You can use a combination of MID and REVERSETEXT functions.

Formula

=REVERSETEXT(MID(REVERSETEXT(A1), 2, 3))

Otherwise, if the length is unknown for the part which you want to retrieve. Then use FIND function to find the comma before that particular word.

=REVERSETEXT(MID(REVERSETEXT(A3), 2, FIND(",", REVERSETEXT(A3)) - 2))