+ Reply to Thread
Results 1 to 7 of 7

extract text from cell left of the second backslash

  1. #1
    Registered User
    Join Date
    03-08-2012
    Location
    New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    9

    extract text from cell left of the second backslash

    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

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2007, 2010
    Posts
    5,060

    Re: extract text from cell left of the second backslash

    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

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    03-08-2012
    Location
    New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: extract text from cell left of the second backslash

    cheers that works.

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

    Cheers

    David

  4. #4
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2007, 2010
    Posts
    5,060

    Re: extract text from cell left of the second backslash

    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

  5. #5
    Registered User
    Join Date
    03-08-2012
    Location
    New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: extract text from cell left of the second backslash

    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

  6. #6
    Forum Contributor
    Join Date
    04-23-2013
    Location
    Abu Dhabi United Arab Emirates
    MS-Off Ver
    Excel 2010
    Posts
    178

    Re: extract text from cell left of the second backslash

    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

  7. #7
    Registered User
    Join Date
    03-08-2012
    Location
    New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: extract text from cell left of the second backslash

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1