extract text from cell left of the second backslash

**dwhite468** · 05-26-2013, 11:12 PM

Hi

I am trying to extract text from a cell, everything left of the second backslash (eg. Refining\Breakdown\ChipSupply\ChipSilo\Screw will become Refining\Breakdown)

I have tried using =LEFT(B33,FIND("\",B33)) but this only gets me past the first backslash

How do I get to the next one in the string?

Cheers

David

**benishiryo** · 05-26-2013, 11:23 PM

hi David, welcome to the forum. add 1 more part:
=LEFT(B33,FIND("\",B33,FIND("\",B33)+1)-1)

you can define the starting number to find the "\". and that would be 1 character after finding the 1st one

**dwhite468** · 05-26-2013, 11:44 PM

cheers that works.

what would I do if I want everything left of the 3rd or even 4th "/"?

Cheers

David

**benishiryo** · 05-26-2013, 11:49 PM

you mean "\"? use SUBSTITUTE to substitute the "\" with a symbol unlikely to appear. i used "^". in SUBSTITUTE, you can indicate the instance number you want to substitute with. so i selected "3" for 3rd in this case. use whatever number required. i then FIND "^" in my substituted text
=LEFT(B33,FIND("^",SUBSTITUTE(B33,"\","^",3))-1)

please mark this as solved if there's nothing else

**dwhite468** · 05-26-2013, 11:55 PM

excellent! thank you that works.

sorry though just one last question, is it possible to make this work the other way round, so showing everything right of the 2nd, 3rd, or 4th "\"?

Cheers

David

**acerrhod** · 05-27-2013, 03:15 AM

Yes is possible, if you want to do as dynamic you need to add a helper column to specify the number of "\" do display or you may change every time the =LEFT(B33,FIND("^",SUBSTITUTE(B33,"\","^",3))-1) [Color in RED] to a desired number.

if dynamic, let say in A33 add a number
A33 B33 c (result of Formula)
2 Refining\Breakdown\ChipSupply\ChipSilo\Screw will become Refining\Breakdown Refining\Breakdown

the formula:
=LEFT(B33,FIND("^",SUBSTITUTE(B33,"\","^",IF(A6<1,1,IF(A33<=(SUM(LEN(B33)-LEN(SUBSTITUTE(B33,"\","")))/LEN("\")),A33,(SUM(LEN(B33)-LEN(SUBSTITUTE(B33,"\","")))/LEN("\"))))))-1)

is too long formula but checking also the maximum number "\" within the text so that it will not give an error

**dwhite468** · 05-27-2013, 07:59 PM

thanks for the help, both answers work for exrtacting text from the left. I no longer have a requirment for getting the right side of the string.

Cheers

David