software

Hi there
I'm an inexperienced excel user but have been given a task to present some
raw data spewed out from a telephone exchange to a note pad file into an
excel spreadsheet. From the outset I've had difficulty in transferring the
data over but have been following the help files provided by office online
and have, up until now, muddled through. My latest problem has had me
tearing my hair out for the last four hours or so. Notepad gives me a six
digit figure eg 105306 which is the time mark for an event ie 10:53 and 6
secs. How can I use this 6 digit figure to generate a time format within a
cell in excel? Every time I try to format the cell it just gives me nonsense
output, something like 00:00:00.
I'm pretty sure it's a boneheaded question for some of you experts but can
anybody give me a clue?
Thanks in advance
Adrian
Hi!

What does 105306 represent?

10 hrs 53 mins 06 secs
10:53:06 AM
10:53:06 PM

Are all the strings 6 digits?

Can you provide more samples?

Biff

quot;Adequot; gt; wrote in message
...
gt; Hi there
gt; I'm an inexperienced excel user but have been given a task to present some
gt; raw data spewed out from a telephone exchange to a note pad file into an
gt; excel spreadsheet. From the outset I've had difficulty in transferring the
gt; data over but have been following the help files provided by office online
gt; and have, up until now, muddled through. My latest problem has had me
gt; tearing my hair out for the last four hours or so. Notepad gives me a six
gt; digit figure eg 105306 which is the time mark for an event ie 10:53 and 6
gt; secs. How can I use this 6 digit figure to generate a time format within a
gt; cell in excel? Every time I try to format the cell it just gives me
gt; nonsense output, something like 00:00:00.
gt; I'm pretty sure it's a boneheaded question for some of you experts but can
gt; anybody give me a clue?
gt; Thanks in advance
gt; Adrian
gt;
gt;

Hello Ade,

You need to use string functions to break the time code apart since
this a non-standard Excel format for time.

VBA EXAMPLE:
Dim H, M, S
Dim TimeCode As String

TimeCode = quot;105306quot;
H = Left(TimeCode, 2) amp; quot;:quot;
M = Mid(TimeCode,3, 2) amp; quot;:quot;
S = Right(TimeCode, 2)

Range(quot;A1quot;) = H amp; M amp; S

A1 will display 10:53:06

There are equivalent Worksheet Functions to do this also.

WORKSHEET FORMULA EXAMPLE:
A1 = 105306
B1 contains the formula =LEFT(A1, 2) amp; quot;:quot; amp; MID(A1, 3, 2) amp; quot;:quot; amp;
RIGHT(A1, 2)

B1 will display 10:53:06

Sincerely,
Leith Ross--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: www.excelforum.com/member.php...oamp;userid=18465
View this thread: www.excelforum.com/showthread...hreadid=503554The following should get them into Excel time format, assuming the 6
digit number is in cell A1:

=VALUE(LEFT(A1,2)amp;quot;:quot;amp;MID(A1,3,2)amp;quot;:quot;amp;RIGHT(A1,2))

Format as time and 13:30:55. Copy the formula down.

I assume that all numbers are 6 digit, i.e. that you have leading
zeroes for a time like 8:15:00.

Hope this helps.

PeteOne way would be to use a formula to create the desired format i.e. 105386 in
cell A1. In B1, LEFT(A1,2)amp;quot;:quot;amp;MID(A1,3,2) would give 10:53 and in C1,
RIGHT(A1,2) would give 06

This may not be what you want if you're hoping to do some calculations with
the results

quot;Adequot; wrote:

gt; Hi there
gt; I'm an inexperienced excel user but have been given a task to present some
gt; raw data spewed out from a telephone exchange to a note pad file into an
gt; excel spreadsheet. From the outset I've had difficulty in transferring the
gt; data over but have been following the help files provided by office online
gt; and have, up until now, muddled through. My latest problem has had me
gt; tearing my hair out for the last four hours or so. Notepad gives me a six
gt; digit figure eg 105306 which is the time mark for an event ie 10:53 and 6
gt; secs. How can I use this 6 digit figure to generate a time format within a
gt; cell in excel? Every time I try to format the cell it just gives me nonsense
gt; output, something like 00:00:00.
gt; I'm pretty sure it's a boneheaded question for some of you experts but can
gt; anybody give me a clue?
gt; Thanks in advance
gt; Adrian
gt;
gt;
gt;

On Fri, 20 Jan 2006 23:45:23 -0000, quot;Adequot; gt;
wrote:

gt;Hi there
gt;I'm an inexperienced excel user but have been given a task to present some
gt;raw data spewed out from a telephone exchange to a note pad file into an
gt;excel spreadsheet. From the outset I've had difficulty in transferring the
gt;data over but have been following the help files provided by office online
gt;and have, up until now, muddled through. My latest problem has had me
gt;tearing my hair out for the last four hours or so. Notepad gives me a six
gt;digit figure eg 105306 which is the time mark for an event ie 10:53 and 6
gt;secs. How can I use this 6 digit figure to generate a time format within a
gt;cell in excel? Every time I try to format the cell it just gives me nonsense
gt;output, something like 00:00:00.
gt;I'm pretty sure it's a boneheaded question for some of you experts but can
gt;anybody give me a clue?
gt;Thanks in advance
gt;Adrian
gt;

You first need to convert that six digit number into something Excel will
understand as a time; and then format that result appropriately:

e.g. with data in A1

B1:=--TEXT(A1,quot;00\:00\:00quot;)

Then Format/Cells/Number/Custom Type: hh:mm:ss--ron

=LEFT(A1,2)amp;quot;:quot;amp;MID(A1,3,2)amp;quot; and quot;amp;IF(MID(A1,5,1)=quot;0quot;,RIGHT(A1,1)amp;quot;
Secsquot;,RIGHT(A1,2)amp;quot; secsquot;)

Vaya con Dios,
Chuck, CABGx3quot;Adequot; gt; wrote in message
...
gt; Hi there
gt; I'm an inexperienced excel user but have been given a task to present some
gt; raw data spewed out from a telephone exchange to a note pad file into an
gt; excel spreadsheet. From the outset I've had difficulty in transferring the
gt; data over but have been following the help files provided by office online
gt; and have, up until now, muddled through. My latest problem has had me
gt; tearing my hair out for the last four hours or so. Notepad gives me a six
gt; digit figure eg 105306 which is the time mark for an event ie 10:53 and 6
gt; secs. How can I use this 6 digit figure to generate a time format within a
gt; cell in excel? Every time I try to format the cell it just gives me
nonsense
gt; output, something like 00:00:00.
gt; I'm pretty sure it's a boneheaded question for some of you experts but can
gt; anybody give me a clue?
gt; Thanks in advance
gt; Adrian
gt;
gt;
Thanks to all of you who replied, it's worked a treat.

All the best
Adequot;CLRquot; gt; wrote in message
...
gt; =LEFT(A1,2)amp;quot;:quot;amp;MID(A1,3,2)amp;quot; and quot;amp;IF(MID(A1,5,1)=quot;0quot;,RIGHT(A1,1)amp;quot;
gt; Secsquot;,RIGHT(A1,2)amp;quot; secsquot;)
gt;
gt; Vaya con Dios,
gt; Chuck, CABGx3
gt;
gt;
gt; quot;Adequot; gt; wrote in message
gt; ...
gt;gt; Hi there
gt;gt; I'm an inexperienced excel user but have been given a task to present
gt;gt; some
gt;gt; raw data spewed out from a telephone exchange to a note pad file into an
gt;gt; excel spreadsheet. From the outset I've had difficulty in transferring
gt;gt; the
gt;gt; data over but have been following the help files provided by office
gt;gt; online
gt;gt; and have, up until now, muddled through. My latest problem has had me
gt;gt; tearing my hair out for the last four hours or so. Notepad gives me a six
gt;gt; digit figure eg 105306 which is the time mark for an event ie 10:53 and 6
gt;gt; secs. How can I use this 6 digit figure to generate a time format within
gt;gt; a
gt;gt; cell in excel? Every time I try to format the cell it just gives me
gt; nonsense
gt;gt; output, something like 00:00:00.
gt;gt; I'm pretty sure it's a boneheaded question for some of you experts but
gt;gt; can
gt;gt; anybody give me a clue?
gt;gt; Thanks in advance
gt;gt; Adrian
gt;gt;
gt;gt;
gt;
gt;