# 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: ")".

• Do you know how many delimiters there might be? Or could there be any number? – paulmorriss Oct 13 '15 at 13:46

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.

• thank you! I did end up finding the answer I needed at here, but your explanation really helps to understand how its working. – Just_Some_Guy Oct 13 '15 at 13:52
• – User15 Oct 13 '15 at 13:56
• @Just_Some_Guy I knew I'd seen this before on here, but search didn't come up with anything. Of course, it was on SO! – Kyle Oct 13 '15 at 13:57

Both of the above would work, but here's something a little more digestible:

``````=TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",LEN(A1))),LEN(A1)))
``````
• This works. It will be more helpful if you explain it. – User15 Oct 13 '15 at 14:01
• @David: This is very well thought. – CFreitas Oct 13 '15 at 14:10
• Like your result, it's best to interpret this right to left. Each instance of the '-' separater is replaced with spaces equal in number to the entire original cell, while preserving everything to the right of the final '-' seperator. This means you end up with a stack of leading spaces at the beginning of your final data element, which can then be trimmed off. – David Vernon Oct 13 '15 at 14:23

Give this a try:

``````=TRIM(IF(ISERROR(FIND("-",A1)),"",MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"-",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))+1,255)))
`````` • This works. It will be more helpful if you explain it. – User15 Oct 13 '15 at 13:59