Text to columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everyone,

I'm trying to import data from Mitrefinch TMS system. Data is in form of
clock in times. Data is copied to clipboard and pasted to excel 2003.
Clocking times are pasted as a batch i.e. 4 separate times in one column
separated by commas.

I have tried to put each time into a separate column using text to columns.
Delimited separated by comma. text qualifier {none} Column format set to
general.

When this has been done only the first column has HH:MM format. I have tried
formatting the columns before doing text to columns and after the data has
been moved to the destination cells.

Help! The only reason I'm doing it is to perform calculations on the
clockings so they have to be formatted.

TIA
 
If your data looks like this:
09:33,10:33,12:01;14:44
Import the data into a temporary table. Then use something like the SQL
below to convert the data to times:

SELECT YourTable.txtTimes,
CDate(Left([TxtTimes],5)) AS FirstTime,
CDate(Mid([TxtTimes],7,5)) AS SecondTime,
CDate(Mid([TxtTimes],13,5)) AS ThirdTime,
CDate(Right([TxtTimes],5)) AS FourthTime
FROM YourTable
WHERE YourTable.txtTimes Is Not Null;

CDate is very, very picky. If any of your times are null or can not be
evaluated as a valid time, it will procduce an error. For example
CDate("12:61") or CDate("2/30/2006") will both cause problems.
 
Thanks for answering. There will definitely be blanks will this affect the
routine?.
--
Smudge


Jerry Whittle said:
If your data looks like this:
09:33,10:33,12:01;14:44
Import the data into a temporary table. Then use something like the SQL
below to convert the data to times:

SELECT YourTable.txtTimes,
CDate(Left([TxtTimes],5)) AS FirstTime,
CDate(Mid([TxtTimes],7,5)) AS SecondTime,
CDate(Mid([TxtTimes],13,5)) AS ThirdTime,
CDate(Right([TxtTimes],5)) AS FourthTime
FROM YourTable
WHERE YourTable.txtTimes Is Not Null;

CDate is very, very picky. If any of your times are null or can not be
evaluated as a valid time, it will procduce an error. For example
CDate("12:61") or CDate("2/30/2006") will both cause problems.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Smudge said:
Hi everyone,

I'm trying to import data from Mitrefinch TMS system. Data is in form of
clock in times. Data is copied to clipboard and pasted to excel 2003.
Clocking times are pasted as a batch i.e. 4 separate times in one column
separated by commas.

I have tried to put each time into a separate column using text to columns.
Delimited separated by comma. text qualifier {none} Column format set to
general.

When this has been done only the first column has HH:MM format. I have tried
formatting the columns before doing text to columns and after the data has
been moved to the destination cells.

Help! The only reason I'm doing it is to perform calculations on the
clockings so they have to be formatted.

TIA
Click to expand...
Click to expand...
 
The "WHERE YourTable.txtTimes Is Not Null" will prevent problems if the whole
field is null; however, if you are missing any of the times, it will not
work. In fact I don't see any way to do it using a query unless the data is
consistant in the number of characters and formatting.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Smudge said:
Thanks for answering. There will definitely be blanks will this affect the
routine?.
--
Smudge


Jerry Whittle said:
If your data looks like this:
09:33,10:33,12:01;14:44
Import the data into a temporary table. Then use something like the SQL
below to convert the data to times:

SELECT YourTable.txtTimes,
CDate(Left([TxtTimes],5)) AS FirstTime,
CDate(Mid([TxtTimes],7,5)) AS SecondTime,
CDate(Mid([TxtTimes],13,5)) AS ThirdTime,
CDate(Right([TxtTimes],5)) AS FourthTime
FROM YourTable
WHERE YourTable.txtTimes Is Not Null;

CDate is very, very picky. If any of your times are null or can not be
evaluated as a valid time, it will procduce an error. For example
CDate("12:61") or CDate("2/30/2006") will both cause problems.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Smudge said:
Hi everyone,

I'm trying to import data from Mitrefinch TMS system. Data is in form of
clock in times. Data is copied to clipboard and pasted to excel 2003.
Clocking times are pasted as a batch i.e. 4 separate times in one column
separated by commas.

I have tried to put each time into a separate column using text to columns.
Delimited separated by comma. text qualifier {none} Column format set to
general.

When this has been done only the first column has HH:MM format. I have tried
formatting the columns before doing text to columns and after the data has
been moved to the destination cells.

Help! The only reason I'm doing it is to perform calculations on the
clockings so they have to be formatted.

TIA
Click to expand...
Click to expand...
Click to expand...
 
Back
Top