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-----
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Daniel
Sent: Monday, May 30, 2011 2:47 PM
To: excel-macros@googlegroups.com
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-----
De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De
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,
On May 27, 5:46 pm, "Daniel" <dcolarde...@free.fr> wrote:
> 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-----
> De : excel-macros@googlegroups.com
> [mailto:excel-macros@googlegroups.com] De 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://twitter.com/exceldailytip2. Join our LinkedIN group
> @http://www.linkedin.com/groups?gid=1871310
> 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
>
> To post to this group, send email to excel-macros@googlegroups.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://twitter.com/exceldailytip 2. Join our LinkedIN group @
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
To post to this group, send email to excel-macros@googlegroups.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://twitter.com/exceldailytip 2. Join our LinkedIN group @
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
To post to this group, send email to excel-macros@googlegroups.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://twitter.com/exceldailytip
2. Join our LinkedIN group @ 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
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
