How to get name of the last folder from folder path

[ThrottleWorks]

Hi,

I have a folder path saved at a cell. Path is something like this.
\\AA\AAA\AAAA\B\BB\BBB\BBBB\C\CCC
I need output result as CCC. Length of string is not fixed. Values mentioned in the above path are dummy. Can anyone please help me in this.

 

[Ramesh]

This will be 2 folded formula:

1) First we need to find the position of the last "\" character.. we can achieve the same by using:

=FIND("@",SUBSTITUTE(A1,"\","@",(LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))/LEN("\")))

2) Then we can use this value and using the Right function we can extract the last part of the given value:

In total, the formula you can use to capture the last value is:

=RIGHT(A1,LEN(A1)-FIND("@",SUBSTITUTE(A1,"\","@",(LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))/LEN("\"))))

Hope this helps!!

 

[Marc L]

Hi Sachin !

Closed to previous formula :​

=RIGHT(A1,LEN(A1)-FIND("|",SUBSTITUTE(A1,"\","|",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))

You like ?
​

 

[deciog]

ThrottleWorks

my contribution

=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",LEN(A1))),LEN(A1)))

Décio

 

[bosco_yip]

Another option :

=MID(A1,LOOKUP(99,FIND("\",A1,ROW($1:$99)))+1,99)

Regards
Bosco

 

[ThrottleWorks]

Hi @Ramesh , @Marc L sir, @deciog and @bosco_yip , thanks a lot for the help.

Have a nice weekend.