worksheet function - Split Excel Cell by last Delimiter

I need to split a cell by the first delimter (reading right to left) so that I can put that value in another cell.

For Example, my data in cell A1 could look like:

"something - more something - a lot more of something - (this is the text I need)"

Cell B1 needs the value "(this is the text I need)"

so far what I have is:

=RIGHT(a1,LEN(FIND(RIGHT("-"),a1)))

but this seems to only return the last char: ")".

Answer

You need this formula:

=MID(A1,FIND("~~~~~",SUBSTITUTE(A1,"-","~~~~~",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))+1,LEN(A1))

Breakdown:

LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))

Counts the number of times "-" occurs within the cell,

SUBSTITUTE(A1,"-","~~~~~",LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))

Then replaces the last occurrence of "-" with "~~~~~". If "~~~~~" has the possibility of occurring normally in your input, use a different value here.

FIND("~~~~~",SUBSTITUTE(A1,"-","~~~~~",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))+1

Finds the position of the "~~~~~" that we just created, and adds one so we start just after it. And then finally, the full formula uses that position and MID() to extract the desired text.