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
    3,051
    Thanks
    145
    Thanked 543 Times in 518 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
    3,051
    Thanks
    145
    Thanked 543 Times in 518 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,733
    Thanks
    126
    Thanked 686 Times in 623 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,291
    Thanks
    3
    Thanked 198 Times in 184 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
    3,051
    Thanks
    145
    Thanked 543 Times in 518 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,733
    Thanks
    126
    Thanked 686 Times in 623 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
  •