Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Sep 2013
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Function substitute

    Hi,

    In Cell E2 I have this E:\Input\Sample\today\001\100\test.pdf The output is test.pdf, but i want the output as 100 how to get that?

    Code:
    =SUBSTITUTE(MID(E2,FIND(CHAR(1),SUBSTITUTE(E2,"\",CHAR(1),LEN(E2)-LEN(SUBSTITUTE(E2,"\",""))-0))+1,255),"\","")

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Goa

    ..see attached file for an array formula that gives you the parent folder name.

    zeddy
    Attached Files Attached Files

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    This is a little longer than zeddy's formula but it is not an array...
    Code:
    =MID(E2,FIND(CHAR(1),SUBSTITUTE(E2,"\",CHAR(1),LEN(E2)-LEN(SUBSTITUTE(E2,"\",""))-1))+1,FIND(CHAR(1),SUBSTITUTE(E2,"\",CHAR(1),LEN(E2)-LEN(SUBSTITUTE(E2,"\",""))))-FIND(CHAR(1),SUBSTITUTE(E2,"\",CHAR(1),LEN(E2)-LEN(SUBSTITUTE(E2,"\",""))-1))-1)
    Steve

  4. #4
    New Lounger
    Join Date
    Sep 2013
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Steve & Zeddy.

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Goa

    I would use Steve's formula in preference to an array formula.

    zeddy

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Here in another formula that will achieve the same results

    Code:
    =TRIM(LEFT(SUBSTITUTE(MID(E2,FIND("|",SUBSTITUTE(E2,"\","|",LEN(E2)-LEN(SUBSTITUTE(E2,"\",""))-1))+1,LEN(E2)),"\",REPT(" ",LEN(E2))),LEN(E2)))
    Maud

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Or something like:
    =TRIM(LEFT(RIGHT(SUBSTITUTE(E2,"\",REPT(" ",250)),500),300))
    given that you're looking at a file path and the parts shouldn't be too long!
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Or you could use a custom Function

    Code:
    Function parentFolder(z)
    zArray = Split(z, "\")
    parentFolder = zArray(UBound(zArray) - 1)
    End Function
    ..and then use this formula:
    =parentFolder(E2)

    zeddy

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Zeddy,

    Now that's the way I would prefer to do it! Sweet!

Posting Permissions

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