# Split Excel Cell by last Delimiter and get Text to left

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)"


also note; there could be dashes else where as follows:

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


Cell B1 needs the value

"something - more something - a lot more of something "


or

 "something - more -something - a lot more of something


in the case of the seccond example

I asked the opposite of this question here: Split Excel Cell by last Delimiter (this question is not the same as the question that I am asking) which gave me the following formula:

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


now I need to do the opposite of this.

• Possible duplicate of Split Excel Cell by last Delimiter – Scott Oct 16 '15 at 22:21
• While I could have figured out what I needed to do from my previous question, the questions are not the same and I'm trying to accomplish 2 different task. – Just_Some_Guy Oct 16 '15 at 22:31

If you look at the answer he gives a good breakdown of how the function is built and it would be easy to get the left side.

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.
Left(A1,FIND("~~~~~",SUBSTITUTE(A1,"-","~~~~~",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))