7

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

1
  • Do you know how many delimiters there might be? Or could there be any number? Oct 13, 2015 at 13:46

3 Answers 3

7

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.

3
  • thank you! I did end up finding the answer I needed at here, but your explanation really helps to understand how its working. Oct 13, 2015 at 13:52
  • Also stackoverflow.com/q/18617349/5108803.
    – User15
    Oct 13, 2015 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, 2015 at 13:57
15

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

=TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",LEN(A1))),LEN(A1)))
3
  • 1
    This works. It will be more helpful if you explain it.
    – User15
    Oct 13, 2015 at 14:01
  • @David: This is very well thought.
    – CFreitas
    Oct 13, 2015 at 14:10
  • 1
    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. Oct 13, 2015 at 14:23
1

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

enter image description here

1
  • This works. It will be more helpful if you explain it.
    – User15
    Oct 13, 2015 at 13:59

You must log in to answer this question.

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