Extract file name from full path using formulas

[rkspeaks]

Chandoo and all Ninjas,


Dasara wishes for everybody,


It's a very Good evening for meeting you all again!


Apropos to the post with the title "Extract file name from full path using formulas", I came up with a problem (not to mention, I always come up with problems), which has a difficulty in extracting the required data. Recently, Our Indian Railway dept. has announced the winter special trains to various destinations in a list of trains, but in a weird manner (off course that's how we get Excelfood). I tried to pull out the Train no, source stn, source stn departure time, destination stn, arrival time as well. They given the following details for the public in an announcement.


Train Number, Train Name, Train Source Stn, Source Dep. Time, Train Destination Stn, Dest. Arr. Time.


My attempts became futile as the data is very irregular. Right then I have seen your post with the title "Extract file name from full path using formulas" like God send.


Could you please help me getting the things sort out. How to face this kind of situation where the data behaves in a weird manner. Is there any solution in Excel for this or not?


http://hotfile.com/dl/177196719/27631f1/Winter_Trains.xlsx.html


Regards

RK

 

[Luke M]

I think your best bet would be to do Text-to-columns, with the comma as the delimiter.

 

[kaushik03]

Hi RK,


Please see the formula for following three parameters:


Train Number

=LEFT(A2,SEARCH(" ",A2)-1) copy it down


Dep. Time

=CONCATENATE(MID(A2,FIND("^^",SUBSTITUTE(A2,":","^^",1))+1-3,3),MID(A2,FIND("^^",SUBSTITUTE(A2,":","^^",1))+1,2)) copy it down


Arr. Time

=MID(A2,FIND(CHAR(22),SUBSTITUTE(A2," ",CHAR(22),LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255) copy it down


PS:A2 is your text


I am working on the rest of the parameters and will get back to you once I will be done.


Regards,

Kaushik

 

[Krishnakumar]

Hi


B2(Helper column),


=SEARCH("spl",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"SPECIAL","spl"),"EXPRESS","SPL"),"EXP","SPL")," SP ","SPL"),"RATH","SPL"))


C2,


=LEFT(A2,FIND(" ",A2)-1)


D2,


=MID(A2,LEN(C2)+2,FIND(" ",A2,B2)-LEN(C2)-1)


E2,


=SUBSTITUTE(LEFT(A2,FIND(":",A2)-3),C2&" "&D2,"")


F2,


=MID(A2,FIND(":",A2)-2,5)


G2,


=MID(A2,FIND(":",A2)+4,LEN(REPLACE(A2,1,FIND(F2,A2)+6,""))-4)


H2,


=RIGHT(A2,5)


Kris

 

[Haseeb A]

Kris,


A minor ... in Train Name, Train Source Stn, when comes EXP SP or SP EXP comes together, like in Row 40, 88, 137, 138, 167, 196, 197


RK,


Try this (small changes in Kris's formula to avoid helper)


B2,


=LEFT(A2,FIND(" ",A2)-1)


C2,


=TRIM(SUBSTITUTE(LEFT(A2,FIND(" ",A2,MAX(INDEX(IFERROR(SEARCH({"Special","exp","SP","SPL","Rath"},A2),""),)))),B2,""))


D2,


=MID(LEFT(A2,FIND(E2,A2)-1),FIND(C2,A2)+LEN(C2)+1,250)


E2,


=MID(A2,FIND(":",A2)-2,5)


F2,


=SUBSTITUTE(MID(A2,FIND(E2,A2)+6,250),G2,"")


G2,


=RIGHT(A2,5)

 

[Krishnakumar]

@ Haseeb nice one.

 

[kaushik03]

Excellent Haseeb...You are the real James Bond of excel....