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.

2

1 Answer 1

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .