## Formula to extract parent folder, parent of parent folder etc

### Formula to extract parent folder, parent of parent folder etc

Does any one have a function or formula to extract for a path for
eg.,

E:\02 My Documents\20 Ebooks and Articles\10.04 Tintin\01 Tintin in
the Land of the Soviets.pdf

Parent Folder: = 10.04 Tintin
Parent.Parent.folder = 20 Ebooks and Articles
Parent.Parent.Parent.Folder= 02 My Documents

any help in this regard would be greatly appreciated.

### Formula to extract parent folder, parent of parent folder etc

Assuming path is in A1 :
Folder :
=MID(A1,LARGE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(INDIRECT("1:"&
LEN(A1))),2)+1,MAX((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(INDIRECT(
"1:"&LEN(A1))))-LARGE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(INDIRE
CT("1:"&LEN(A1))),2)-1)
Parent folder :
=MID(A1,LARGE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(INDIRECT("1:"&
LEN(A1))),3)+1,LARGE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(INDIREC
T("1:"&LEN(A1))),2)-1)
Parent parent folder :
=MID(A1,LARGE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(INDIRECT("1:"&
LEN(A1))),4)+1,LARGE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(INDIREC
T("1:"&LEN(A1))),3)-1)

Daniel

-----Message d'origine-----

la part de Jhoomla
Envoy?: vendredi 27 mai 2011 07:57
?: MS EXCEL AND VBA MACROS
Objet?: \$\$Excel-Macros\$\$ Formula to extract parent folder, parent of parent
folder etc

Does any one have a function or formula to extract for a path for eg.,

E:\02 My Documents\20 Ebooks and Articles\10.04 Tintin\01 Tintin in the Land
of the Soviets.pdf

Parent Folder: = 10.04 Tintin
Parent.Parent.folder = 20 Ebooks and Articles Parent.Parent.Parent.Folder=
02 My Documents

any help in this regard would be greatly appreciated.

--
----------------------------------------------------------------------------
------
Some important links for excel users:

3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and
Tricks at http://exceldailytip.blogspot.com

<><><><><><><><><><><><><><><><><><><><><><>

### Formula to extract parent folder, parent of parent folder etc

Better :
Parent folder :
=MID(A1,LARGE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(INDIRECT("1:"&
LEN(A1))),3)+1,LARGE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(INDIREC
T("1:"&LEN(A1))),2)-LARGE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(IN
DIRECT("1:"&LEN(A1))),3)-1)
Parent parent folder :
=MID(A1,LARGE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(INDIRECT("1:"&
LEN(A1))),4)+1,LARGE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(INDIREC
T("1:"&LEN(A1))),3)-LARGE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(IN
DIRECT("1:"&LEN(A1))),4)-1)

Daniel

-----Message d'origine-----

la part de Jhoomla
Envoy?: vendredi 27 mai 2011 07:57
?: MS EXCEL AND VBA MACROS
Objet?: \$\$Excel-Macros\$\$ Formula to extract parent folder, parent of parent
folder etc

Does any one have a function or formula to extract for a path for eg.,

E:\02 My Documents\20 Ebooks and Articles\10.04 Tintin\01 Tintin in the Land
of the Soviets.pdf

Parent Folder: = 10.04 Tintin
Parent.Parent.folder = 20 Ebooks and Articles Parent.Parent.Parent.Folder=
02 My Documents

any help in this regard would be greatly appreciated.

--
----------------------------------------------------------------------------
------
Some important links for excel users:

3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and
Tricks at http://exceldailytip.blogspot.com

<><><><><><><><><><><><><><><><><><><><><><>

### Formula to extract parent folder, parent of parent folder etc

try this formula
=*SUBSTITUTE(B3*
,MID(B3,1+FIND("^",SUBSTITUTE(B3,"\","^",LEN(B3)-LEN(SUBSTITUTE(B3,"\",""))))-1,999),"")

and see the attachment if it helps

> Does any one have a function or formula to extract for a path for
> eg.,

> E:\02 My Documents\20 Ebooks and Articles\10.04 Tintin\01 Tintin in
> the Land of the Soviets.pdf

> Parent Folder: = 10.04 Tintin
> Parent.Parent.folder = 20 Ebooks and Articles
> Parent.Parent.Parent.Folder= 02 My Documents

> any help in this regard would be greatly appreciated.

### Formula to extract parent folder, parent of parent folder etc

Hi setiyowati,

Thanks for the effort. This helps . I needed a formula to extract only
the folder name, while your formula extracts the whole path. I used
another formula available in the net to extract  file name in another
cell. ie., =MID(A25,FIND("*",SUBSTITUTE(A25,"\","*",LEN(A25)-
LEN(SUBSTITUTE(A25,"\",""))))+1,LEN(A25))

regards,

> try this formula
> =*SUBSTITUTE(B3*
> ,MID(B3,1+FIND("^",SUBSTITUTE(B3,"\","^",LEN(B3)-LEN(SUBSTITUTE(B3,"\",""))))-1,999),"")

> and see the attachment if it helps

> > Does any one have a function or formula to extract for a path for
> > eg.,

> > E:\02 My Documents\20 Ebooks and Articles\10.04 Tintin\01 Tintin in
> > the Land of the Soviets.pdf

> > Parent Folder: = 10.04 Tintin
> > Parent.Parent.folder = 20 Ebooks and Articles
> > Parent.Parent.Parent.Folder= 02 My Documents

> > any help in this regard would be greatly appreciated.

> ?ctv_File Path and his parents.xls

### Formula to extract parent folder, parent of parent folder etc

Hi Daniel

Thank you for the effort. When I use the formula, I am getting #NUM!
error. I am not able to understand what is wrong.

Would you be able to attach an excel file which is working. I am using
excel 2007.

regards,

> Assuming path is in A1 :
> Folder :
> =MID(A1,LARGE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(INDIRECT("1:"&
> LEN(A1))),2)+1,MAX((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(INDIRECT(
> "1:"&LEN(A1))))-LARGE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(INDIRE
> CT("1:"&LEN(A1))),2)-1)
> Parent folder :
> =MID(A1,LARGE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(INDIRECT("1:"&
> LEN(A1))),3)+1,LARGE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(INDIREC
> T("1:"&LEN(A1))),2)-1)
> Parent parent folder :
> =MID(A1,LARGE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(INDIRECT("1:"&
> LEN(A1))),4)+1,LARGE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(INDIREC
> T("1:"&LEN(A1))),3)-1)

> Daniel

> -----Message d'origine-----

> la part de Jhoomla
> Envoy?: vendredi 27 mai 2011 07:57
> ?: MS EXCEL AND VBA MACROS
> Objet?: \$\$Excel-Macros\$\$ Formula to extract parent folder, parent of parent
> folder etc

> Does any one have a function or formula to extract for a path for eg.,

> E:\02 My Documents\20 Ebooks and Articles\10.04 Tintin\01 Tintin in the Land
> of the Soviets.pdf

> Parent Folder: = 10.04 Tintin
> Parent.Parent.folder = 20 Ebooks and Articles Parent.Parent.Parent.Folder=
> 02 My Documents

> any help in this regard would be greatly appreciated.

> --
> ----------------------------------------------------------------------------
> ------
> Some important links for excel users:

> 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> 4. Learn VBA Macros athttp://www.quickvba.blogspot.com5. Excel Tips and
> Tricks athttp://exceldailytip.blogspot.com

> <><><><><><><><><><><><><><><><><><><><><><>

### Formula to extract parent folder, parent of parent folder etc

Sorry, I should have said that these are array formulas (validate with
Ctrl+Shift+Enter). Have a look at the attached file.

Regards.

Daniel

-----Message d'origine-----

la part de Jhoomla
Envoy?: lundi 30 mai 2011 06:46
?: MS EXCEL AND VBA MACROS
Objet?: Re: \$\$Excel-Macros\$\$ Formula to extract parent folder, parent of
parent folder etc

Hi Daniel

Thank you for the effort. When I use the formula, I am getting #NUM!
error. I am not able to understand what is wrong.

Would you be able to attach an excel file which is working. I am using excel
2007.

regards,

> Assuming path is in A1 :
> Folder :
> =MID(A1,LARGE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(INDIRECT
> ("1:"&
> LEN(A1))),2)+1,MAX((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(IND
> IRECT(
> "1:"&LEN(A1))))-LARGE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(
> INDIRE
> CT("1:"&LEN(A1))),2)-1)
> Parent folder :
> =MID(A1,LARGE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(INDIRECT
> ("1:"&
> LEN(A1))),3)+1,LARGE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(I
> NDIREC
> T("1:"&LEN(A1))),2)-1)
> Parent parent folder :
> =MID(A1,LARGE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(INDIRECT
> ("1:"&
> LEN(A1))),4)+1,LARGE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(I
> NDIREC
> T("1:"&LEN(A1))),3)-1)

> Daniel

> -----Message d'origine-----

> vendredi 27 mai 2011 07:57 ?: MS EXCEL AND VBA MACROS Objet?:
> \$\$Excel-Macros\$\$ Formula to extract parent folder, parent of parent
> folder etc

> Does any one have a function or formula to extract for a path for eg.,

> E:\02 My Documents\20 Ebooks and Articles\10.04 Tintin\01 Tintin in
> the Land of the Soviets.pdf

> Parent Folder: = 10.04 Tintin
> Parent.Parent.folder = 20 Ebooks and Articles
> Parent.Parent.Parent.Folder=
> 02 My Documents

> any help in this regard would be greatly appreciated.

> --
> ----------------------------------------------------------------------
> ------
> ------
> Some important links for excel users:

> 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> 4. Learn VBA Macros athttp://www.quickvba.blogspot.com5. Excel Tips
> and Tricks athttp://exceldailytip.blogspot.com

> <><><><><><><><><><><><><><><><><><><><><><>

--
----------------------------------------------------------------------------
------
Some important links for excel users:

3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and
Tricks at http://exceldailytip.blogspot.com

<><><><><><><><><><><><><><><><><><><><><><>

### Formula to extract parent folder, parent of parent folder etc

Hi Amith,

See the attached file.
Let me know in case of any queries.

Best Regards,
DILIPandey

> Does any one have a function or formula to extract for a path for
> eg.,

> E:\02 My Documents\20 Ebooks and Articles\10.04 Tintin\01 Tintin in
> the Land of the Soviets.pdf

> Parent Folder: = 10.04 Tintin
> Parent.Parent.folder = 20 Ebooks and Articles
> Parent.Parent.Parent.Folder= 02 My Documents

> any help in this regard would be greatly appreciated.

> --
> ----------------------------------------------------------------------------------
> Some important links for excel users:

> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

> <><><><><><><><><><><><><><><><><><><><><><>

--
Thanks & Regards,

DILIP KUMAR PANDEY, mvp
MBA,B.Com(Hons),BCA
Mobile: +91 9810929744

New Delhi - 62, India

### Formula to extract parent folder, parent of parent folder etc

Hi
See if it Help

Sub Extract()

Range("A1").Copy Range("A4")
Application.CutCopyMode = False
Range("A4").Select
Selection.TextToColumns Destination:=Range("A4"), DataType:=xlDelimited,
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar
_
:="\", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1),
Array(4, 1), Array(5, _
1)), TrailingMinusNumbers:=True
Range("A4").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("D6").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Rows(4).ClearContents
End Sub

-----Original Message-----

On Behalf Of Daniel
Sent: Monday, May 30, 2011 2:47 PM

Subject: RE: \$\$Excel-Macros\$\$ Formula to extract parent folder, parent of
parent folder etc

Sorry, I should have said that these are array formulas (validate with
Ctrl+Shift+Enter). Have a look at the attached file.

Regards.

Daniel

-----Message d'origine-----

la part de Jhoomla Envoy?: lundi 30 mai 2011 06:46 ?: MS EXCEL AND VBA
MACROS Objet?: Re: \$\$Excel-Macros\$\$ Formula to extract parent folder, parent
of parent folder etc

Hi Daniel

Thank you for the effort. When I use the formula, I am getting #NUM!
error. I am not able to understand what is wrong.

Would you be able to attach an excel file which is working. I am using excel
2007.

regards,

> Assuming path is in A1 :
> Folder :
> =MID(A1,LARGE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(INDIRECT
> ("1:"&
> LEN(A1))),2)+1,MAX((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(IND
> IRECT(
> "1:"&LEN(A1))))-LARGE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(
> INDIRE
> CT("1:"&LEN(A1))),2)-1)
> Parent folder :
> =MID(A1,LARGE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(INDIRECT
> ("1:"&
> LEN(A1))),3)+1,LARGE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(I
> NDIREC
> T("1:"&LEN(A1))),2)-1)
> Parent parent folder :
> =MID(A1,LARGE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(INDIRECT
> ("1:"&
> LEN(A1))),4)+1,LARGE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(I
> NDIREC
> T("1:"&LEN(A1))),3)-1)

> Daniel

> -----Message d'origine-----

> vendredi 27 mai 2011 07:57 ?: MS EXCEL AND VBA MACROS Objet?:
> \$\$Excel-Macros\$\$ Formula to extract parent folder, parent of parent
> folder etc

> Does any one have a function or formula to extract for a path for eg.,

> E:\02 My Documents\20 Ebooks and Articles\10.04 Tintin\01 Tintin in
> the Land of the Soviets.pdf

> Parent Folder: = 10.04 Tintin
> Parent.Parent.folder = 20 Ebooks and Articles
> Parent.Parent.Parent.Folder=
> 02 My Documents

> any help in this regard would be greatly appreciated.

> --
> ----------------------------------------------------------------------
> ------
> ------
> Some important links for excel users:

> 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> 4. Learn VBA Macros athttp://www.quickvba.blogspot.com5. Excel Tips
> and Tricks athttp://exceldailytip.blogspot.com

> <><><><><><><><><><><><><><><><><><><><><><>

--
----------------------------------------------------------------------------
------
Some important links for excel users:

3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and
Tricks at http://exceldailytip.blogspot.com

<><><><><><><><><><><><><><><><><><><><><><>

--
----------------------------------------------------------------------------
------
Some important links for excel users:

3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and
Tricks at http://exceldailytip.blogspot.com

<><><><><><><><><><><><><><><><><><><><><><>

### Formula to extract parent folder, parent of parent folder etc

Thanks, this helps.

regards,

Amith

> Hi Amith,

> See the attached file.
> Let me know in case of any queries.

> Best Regards,
> DILIPandey

> > Does any one have a function or formula to extract for a path for
> > eg.,

> > E:\02 My Documents\20 Ebooks and Articles\10.04 Tintin\01 Tintin in
> > the Land of the Soviets.pdf

> > Parent Folder: = 10.04 Tintin
> > Parent.Parent.folder = 20 Ebooks and Articles
> > Parent.Parent.Parent.Folder= 02 My Documents

> > any help in this regard would be greatly appreciated.

> > --
> > ----------------------------------------------------------------------------------
> > Some important links for excel users:

> > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com

> > <><><><><><><><><><><><><><><><><><><><><><>

> --
> Thanks & Regards,

> DILIP KUMAR PANDEY, mvp
> ? ? ? ?MBA,B.Com(Hons),BCA
> Mobile: +91 9810929744

> New Delhi - 62, India

> ?Parent Folders by DILIPandey.xls

Problem: I have a user object (uo_1) with a script that says:

parent.triggerevent('ue_save')

This works fine when I insert the user object in a window. But when I
insert the user object in another user object (uo_2), parent in the
uo_1 script returns uo_2, not the window.

How do I generically refer to the current window? I tried
GetActiveSheet(), but that doesn't work if the window is not a sheet.