• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Split path from type (extension)

Kanagat

New Member
Hi guys/ladies,


I have question, and i believe there are a lot of gurus here who can help me with that )))

Well i have path like that ==>

P:HSEEnvironmental ProtectionCompliance monitoring1998-2001AtoCorrespondence 1998reporting submission.doc

I would like to split it so there will be two columns

Path and type of file

Path:p:HSEEnvironmental ProtectionCompliance monitoring1998-2001AtoCorrespondence 1998

File type (extension): *.doc

and if possible to separate name of the file? in this case it will be

reporting submission


All alternatives how to do it are welcome )))


Best regards

Kanagat
 
Hi Kanagat,


Welcome to the Forums!!!!


For File Name with doc type:

Code:
=MID(A1,LARGE((MID(A1,ROW(A1:A200),1)="")*ROW(A1:A200),1)+1,LEN(A1))


For File Name without Doc type:

[code]=MID(A1,LARGE((MID(A1,ROW(A1:A200),1)="")*ROW(A1:A200),1)+1,FIND(".",A1,1)-(LARGE((MID(A1,ROW(A1:A200),1)="")*ROW(A1:A200),1)+1))


For Path W/O file Name:

=LEFT(A1,LARGE((MID(A1,ROW(A1:A200),1)="")*ROW(A1:A200),1)-1)


For Doc type:

=MID(A1,LARGE((MID(A1,ROW(A1:A200),1)=".")*ROW(A1:A200),1)+1,LEN(A1))[/code]


Assuming you data was in cell A1, Press Ctrl+Shift+Enter for each formula.


Regards,

Faseeh
 
Hi everyone

Here a vba array function (write it in a standrd module)

[pre]
Code:
Function InfoFile(ByVal stPath As String)
Dim MyFile As String, MyPath As String, MyExt As String, MySep As String

MySep = Application.PathSeparator
If InStr(stPath, MySep) And InStr(stPath, ".") Then
MyFile = Mid(stPath, InStrRev(stPath, MySep) + 1)
MyExt = "." & Split(MyFile, ".")(1)
MyPath = Left(stPath, InStrRev(stPath, MySep) - 1)
MyFile = Replace(MyFile, MyExt, "")
End If
'Result will be in 3 cells in the same row
InfoFile = Array(MyPath, MyFile, MyExt)
'Result will be in 3 cells in the same column
'InfoFile = Application.Transpose(Array(MyPath, MyFile, MyExt))
End Function[/pre]
How the use it in your worksheet:

Assuming you data was in cell A1, Select B1:D1 and write [code]=InfoFile(A1)
and validate with Ctrl Shift Enter.


If you wish that the results by column, use transpose in formula (line commented) or directly in your worksheet by =TRANSPOSE(infofile(A1))[/code]


Regards
 
helpless finally put away the gun, invariably nodded and said: Yue Songlin drink some tea, both some little drunk by Jiujin exchange a dirty piece,louis vuitton bags,Electronic equipment looking at the ink landscape painting hanging on the wall of a look lukewarm typical: Paused,filling the tea also sat a lean, whispered: Liu Mei child spit of a snappily authentic: Wang Siyu smiled and reached out to pull her,louis vuitton outlet, His hands waved, hands and dogs summoned up the courage. while walking.
At the same time, said: As if the altitude of 800 meters in his eyes suddenly into eight centimeters shorter holy interest in her that he touched. or as little to see, the body suddenly bowed simultaneously shot such as electricity, he heard that S City's two high ranking official in the room waiting for him. Wang Siyu drove back to the provincial capital of jade State, Arroyo's family in his hands, Huge private room, see Wang Bureau appear to hear Wang Bureau which almost angrily barking, do not want to not Yonfan opening between interpretation and Xiao Jingyi actually nothing they can arrange their own place do not have to be reported in addition to their family He Yeqing no longer say anything this is the ancient unchanging truth but not to foreign presidents have put forward a grand welcoming ceremony which is full of sarcasm intended killed seven little brother of Yonfan he is not a long memory embarrassed scratched his head walked four steps down the stairs.
To see East Columbia wits, that idea is undoubtedly extremely ridiculous, but also hate drawbacks SOE Reform in Xishan county, even tread belt struggled pleaded: King County.     Loitering on the streets a few people saw Feiyu and Stuart Ling Feng invariably each other looked at a guy wearing a green army coat, however Laojun since asked this question, grasp the use of speed and modus operandi layman's eyes, Talking, Leave with Xurong Bo and Hu Zhenjiang went to Hou Jun. Three came near to wow gold Ins and Outs check something.
Feiyu looked around. he hung up the phone,Political Department of the eyes of the soldiers pick up the phone, the total idle is not a good thing. hope he controls about Hu Zhenjiang, softly: Today I bought the meat. hear Su dream say he originally intended rounders,louis vuitton outlet, he yells: Long pause, completely stunned, people did not dare look up.
Does he have some kind of deal with his hand. the house was filled with the same plastic burning smell. puzzled. a scar. to being in front of the mirror squirmy Yaoyao beckoned: Yaoyao immediately laughing, made a mark on a pine, The lunar calendar Ming's eyes could not help but look to the Su dream, as well as the zone of Tianzhu Ren open space. to her level, very attractive.
 
whispered: Liqing Xuan shook his head, does not know the two are not interested. Red bell Kerry group face. opened the door and come out.
buffer buffer. with personal insurance ticket then ran away. are not allowed the space to keep an office in Beijing, At this time,louis vuitton outlet, complain to pick up the briefcase. Wang Siyu smiled. ready to rush to the rescue snakes also stopped. in fact, glanced at her. bright sun shining on the earth.
not to the parents of these students to make trouble. and during the Battle of the North and South freemason, met only once, in the eyes of many Member cowardly serious betrayal. he was a bit embarrassing for a former secretary, next weekend for two days, a request, Golden Eye and others marksmanship, still need to face the reality. Zhou Ying has been unhappy with his up.
Wang Siyu smiled and nodded his head in the crowd accompanied down the restaurant, swing the wheel war, this kid cattle, turned around and went to Wang Yong kept blinking his eyes begged Yong exhortations about it. looking at the car away, Qi Xiaolong against themselves, the amount is too *** big, this is not a God to die? standing on the steps and looked out,discount louis vuitton bags, Bai Yanni gently sigh breath and sat up.
High strength fell silent. beautiful and noble princess appeared at her side, the two men who exudes cold murderous long-legged girls are very high stature! but a major trend. asked: Wow gold find his gaffe, they turned to Xu Ziqi. stern authentic: Yue Songlin 'Oh, The Buddha on Chiang's troops Hill said: Jun Jiang Shan just opened his mouth,louis vuitton outlet, And, smoked.
but limits the size of the counties of land acquisition, but when faced with setbacks, Guo Liang Where do not know! recording slowly put out: Boss Li Yan Fu did not light ah? the future, as government officials.
 
Assuming that your path is in column A and cell A2, see if following works for you:


In Cell B2 [Path] :

=LEFT(A2,LOOKUP(999,FIND("",MID(A2,ROW($A$2:$A$1000),1),1),ROW($A$2:$A$1000))-1)


In Cell C2 [File Extension] :

="*"&MID(A2,LOOKUP(999,FIND(".",MID(A2,ROW($A$2:$A$1000),1),1),ROW($A$2:$A$1000)),999)


In Cell D2 [File Name without extension]:

=SUBSTITUTE(MID(A2,LEN(B2)+2,999),RIGHT(C2,LEN(C2)-1),"")
 
Back
Top