1. ## 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. Hi Goa

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

zeddy

3. 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. Thanks Steve & Zeddy.

5. Hi Goa

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

zeddy

6. 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. 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!

8. 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. 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
•