Formula to extract parent folder, parent of parent folder etc

Formula to extract parent folder, parent of parent folder etc

Post by Jhooml » Sat, 28 May 2011 14:56:53



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

Post by Danie » Sat, 28 May 2011 21:46:42


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:
1. Follow us on TWITTER for tips tricks and links :

http://www.linkedin.com/groups?gid=1871310
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


<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

 
 
 

Formula to extract parent folder, parent of parent folder etc

Post by Danie » Sat, 28 May 2011 22:24:50


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:
1. Follow us on TWITTER for tips tricks and links :

http://www.linkedin.com/groups?gid=1871310
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


<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

 
 
 

Formula to extract parent folder, parent of parent folder etc

Post by STDEV(i » Sun, 29 May 2011 10:59:53


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
20K Download
 
 
 

Formula to extract parent folder, parent of parent folder etc

Post by Jhooml » Tue, 31 May 2011 13:39:28


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
> 20KViewDownload

 
 
 

Formula to extract parent folder, parent of parent folder etc

Post by Jhooml » Tue, 31 May 2011 13:46:02


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


> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below linkhttp://www.facebook.com/discussexcel

 
 
 

Formula to extract parent folder, parent of parent folder etc

Post by Danie » Tue, 31 May 2011 18:16:49


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:
> 1. Follow us on TWITTER for tips tricks and links
> :http://twitter.com/exceldailytip2. Join our LinkedIN group

> 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


> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below
> linkhttp://www.facebook.com/discussexcel

--
----------------------------------------------------------------------------
------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :

http://www.linkedin.com/groups?gid=1871310
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


<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

  ExtractFolders.xls
33K Download
 
 
 

Formula to extract parent folder, parent of parent folder etc

Post by Dilip Pande » Wed, 01 Jun 2011 02:05:59


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:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip

> 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


> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel

--
Thanks & Regards,

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


New Delhi - 62, India

  Parent Folders by DILIPandey.xls
18K Download
 
 
 

Formula to extract parent folder, parent of parent folder etc

Post by Rajan_Verm » Wed, 01 Jun 2011 13:51:10


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:
> 1. Follow us on TWITTER for tips tricks and links
> :http://twitter.com/exceldailytip2. Join our LinkedIN group

> 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


> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below
> linkhttp://www.facebook.com/discussexcel

--
----------------------------------------------------------------------------
------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :

http://www.linkedin.com/groups?gid=1871310
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


<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

--
----------------------------------------------------------------------------
------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :

http://www.linkedin.com/groups?gid=1871310
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


<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

 
 
 

Formula to extract parent folder, parent of parent folder etc

Post by Jhooml » Fri, 03 Jun 2011 13:52:38


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:
> > 1. Follow us on TWITTER for tips tricks and links :
> >http://twitter.com/exceldailytip

> > 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


> > <><><><><><><><><><><><><><><><><><><><><><>
> > Like our page on facebook , Just follow below link
> >http://www.facebook.com/discussexcel

> --
> Thanks & Regards,

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


> New Delhi - 62, India

> ?Parent Folders by DILIPandey.xls
> 18KViewDownload

 
 
 

1. parent of parent

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.

2. UnixWare Frequently Asked Questions (General)

3. The Parent of the Parent

4. Eliminate X-axis labeling

5. Where's the Parent Folder Button?

6. FAQ: All about Amiga FTP

7. programmatically view current folder's parent

8. Forcing unicast for all EIGRP packets?

9. How to retrieve the parent folder from IShellFolder?

10. Parent of a soon to be owner of an xbox?

11. Overprotective parents

12. HELP ON LOCK PARENT CODE!!!! NEED OVER RIDE

13. How do I convince my parents............