3

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

  • Do you know how many delimiters there might be? Or could there be any number? – paulmorriss Oct 13 '15 at 13:46
5

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.

  • 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 '15 at 13:52
  • Also stackoverflow.com/q/18617349/5108803. – User15 Oct 13 '15 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 '15 at 13:57
8

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

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

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

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.