0

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
1

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.

From the other answer:

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.

So you now have the position of the last -, add in a left function and you have your function

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.