Search code examples
excelexcel-formulasubstringexcel-2013

Extract substring from this string inside Excel cell

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.

Solution

  • 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.