how to convert .txt file to excel file?

Welcome Guest! To enable all features please Login or Register.

Notification

Error

how to convert .txt file to excel file? - how to convert .txt file to excel file?

Options

Previous Topic Next Topic

buyandsell		#1 Posted : Wednesday, June 9, 2010 4:31:26 AM(UTC)
Rank: Member Groups: Registered, Registered Users Joined: 10/12/2008(UTC) Posts: 12		(.txt file) (date and time have not separated) 20100602 094500; 19577; 19620; 19558; 19609; 895 20100602 094601; 19606; 19686; 19606; 19650; 825 20100602 094700; 19647; 19665; 19640; 19654; 617 (excel file) (date and time have separated) 2/6/2010 09:45:00 19577 19620 19558 19609 895 2/6/2010 09:46:01 19606 19686 19606 19650 825 2/6/2010 09:47:00 19647 19665 19640 19654 617


User Profile View All Posts by User View Thanks


	Wanna join the discussion?! Login to your Discussions forum account or Register a new forum account.

haddison		#2 Posted : Wednesday, June 9, 2010 10:39:46 AM(UTC)
Rank: Advanced Member Groups: Registered, Registered Users, Subscribers Joined: 3/6/2010(UTC) Posts: 113 Location: London		1. Copy the text into Column A 2. Copy into Cell B1: =MID(A1,7,2)&"/"&MID(A1,5,2)&"/"&MID(A1,1,4) 3. Copy into Cell C1: =MID(A1,10,2)&":"&MID(A1,12,2)&":"&MID(A1,14,2) 4. Copy into Cell D1: =MID(A1,18,FIND(";",A1,17)-18) 5. Copy into Cell E1: =LEFT(RIGHT(A1,LEN(A1)-FIND(";",A1,19)-1),FIND(";",RIGHT(A1,LEN(A1)-FIND(";",A1,19)-1),1)-1) 6. Copy into Cell F1: =LEFT(RIGHT(A1,LEN(A1)-FIND(";",A1,19+FIND(";",RIGHT(A1,LEN(A1)-FIND(";",A1,19)-1),1))-1),FIND(";",RIGHT(A1,LEN(A1)-FIND(";",A1,19+FIND(";",RIGHT(A1,LEN(A1)-FIND(";",A1,19)-1),1))-1),1)-1) 7. Copy into Cell G1: =LEFT(RIGHT(A1,LEN(A1)-FIND(";",A1,19+FIND(";",RIGHT(A1,LEN(A1)-FIND(";",A1,19)-1),1)+FIND(";",RIGHT(A1,LEN(A1)-FIND(";",A1,19+FIND(";",RIGHT(A1,LEN(A1)-FIND(";",A1,19)-1),1))-1),1))-1),FIND(";",RIGHT(A1,LEN(A1)-FIND(";",A1,19+FIND(";",RIGHT(A1,LEN(A1)-FIND(";",A1,19)-1),1)+FIND(";",RIGHT(A1,LEN(A1)-FIND(";",A1,19+FIND(";",RIGHT(A1,LEN(A1)-FIND(";",A1,19)-1),1))-1),1))-1),1)-1) 8. Copy into Cell H1: =RIGHT(A1,LEN(A1)-FIND(";",A1,19+FIND(";",RIGHT(A1,LEN(A1)-FIND(";",A1,19)-1),1)+FIND(";",RIGHT(A1,LEN(A1)-FIND(";",A1,19+FIND(";",RIGHT(A1,LEN(A1)-FIND(";",A1,19)-1),1))-1),1)+FIND(";",RIGHT(A1,LEN(A1)-FIND(";",A1,19+FIND(";",RIGHT(A1,LEN(A1)-FIND(";",A1,19)-1),1)+FIND(";",RIGHT(A1,LEN(A1)-FIND(";",A1,19+FIND(";",RIGHT(A1,LEN(A1)-FIND(";",A1,19)-1),1))-1),1))-1),1)+1)-1) The formulae are really long because Excel has to find the location of the semi-colon. It is therefore robust and will work even if the prices are more or less than 5 figures and the volume more or less than 3 figures. I assume you want UK format dates. Are the zeros in the day and month OK? If not, I'll need to change the formula. Best regards H


User Profile View All Posts by User View Thanks

buyandsell		#3 Posted : Thursday, June 10, 2010 3:10:24 AM(UTC)
Rank: Member Groups: Registered, Registered Users Joined: 10/12/2008(UTC) Posts: 12		It is ok, thanks!


User Profile View All Posts by User View Thanks

Users browsing this topic
Guest (Hidden)

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.