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

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

3 Answers 3

Reset to default
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.

Improve this answer
3
  • 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, 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)))
Improve this answer
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.
    – David Vernon
    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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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