editing numbers

[stremetzky]

I have imorted values from another program. Imported values are in
000:00 form (000 for hours and 00 for minutes). If there is less than
100 hours the value is still not treated as [h]:mm. For example:

029:15

How can I erase the first one or two zeros??

stremetzky

 

[Guest]

Make sure you don't have text. check for signgle quote att beginning of time.

 

[David Biddulph]

I think you'll find that it will default to text, without the need for a
single quote. A number of the normal tricks for converting to time won't
work.

If all else fails, try
=LEFT(A1,FIND(":",A1)-1)/24+RIGHT(A1,LEN(A1)-FIND(":",A1))/24/60
(or, if the number of characters is fixed, =LEFT(A1,3)/24+RIGHT(A1,2)/24/60)
and format as [h]:mm
--
David Biddulph

Make sure you don't have text. check for signgle quote att beginning of
time.

I have imorted values from another program. Imported values are in
000:00 form (000 for hours and 00 for minutes). If there is less than
100 hours the value is still not treated as [h]:mm. For example:

029:15

How can I erase the first one or two zeros??

stremetzky

 

[Guest]

I think they want time, and got text instead. They need to convert from text
tto time.

I think you'll find that it will default to text, without the need for a
single quote. A number of the normal tricks for converting to time won't
work.

If all else fails, try
=LEFT(A1,FIND(":",A1)-1)/24+RIGHT(A1,LEN(A1)-FIND(":",A1))/24/60
(or, if the number of characters is fixed, =LEFT(A1,3)/24+RIGHT(A1,2)/24/60)
and format as [h]:mm
--
David Biddulph

Make sure you don't have text. check for signgle quote att beginning of
time.

I have imorted values from another program. Imported values are in
000:00 form (000 for hours and 00 for minutes). If there is less than
100 hours the value is still not treated as [h]:mm. For example:

029:15

How can I erase the first one or two zeros??

stremetzky

 

[David Biddulph]

Can you suggest an easier way of converting 029:15 from text to time, Joel?
I feel there ought to be an easier way, but the usual ways don't seem to
work in this case.
--
David Biddulph

I think they want time, and got text instead. They need to convert from
text
tto time.

I think you'll find that it will default to text, without the need for a
single quote. A number of the normal tricks for converting to time won't
work.

If all else fails, try
=LEFT(A1,FIND(":",A1)-1)/24+RIGHT(A1,LEN(A1)-FIND(":",A1))/24/60
(or, if the number of characters is fixed,
=LEFT(A1,3)/24+RIGHT(A1,2)/24/60)
and format as [h]:mm
--
David Biddulph

Make sure you don't have text. check for signgle quote att beginning
of
time.

:

I have imorted values from another program. Imported values are in
000:00 form (000 for hours and 00 for minutes). If there is less than
100 hours the value is still not treated as [h]:mm. For example:

029:15

How can I erase the first one or two zeros??

stremetzky

 

[Guest]

Not sure if this is any better. the real problem is sttripping off leading
zeros

=TIMEVALUE(trimchar(E16,"0"))

wrote my own function to strip off leading zeroes

Function trimchar(MyString As String, MyChar As String) As String

trimchar = MyString
Do While Left(trimchar, 1) = MyChar
trimchar = Mid(trimchar, 2)
Loop
End Function

Can you suggest an easier way of converting 029:15 from text to time, Joel?
I feel there ought to be an easier way, but the usual ways don't seem to
work in this case.
--
David Biddulph

I think they want time, and got text instead. They need to convert from
text
tto time.

I think you'll find that it will default to text, without the need for a
single quote. A number of the normal tricks for converting to time won't
work.

If all else fails, try
=LEFT(A1,FIND(":",A1)-1)/24+RIGHT(A1,LEN(A1)-FIND(":",A1))/24/60
(or, if the number of characters is fixed,
=LEFT(A1,3)/24+RIGHT(A1,2)/24/60)
and format as [h]:mm
--
David Biddulph

Make sure you don't have text. check for signgle quote att beginning
of
time.

:

I have imorted values from another program. Imported values are in
000:00 form (000 for hours and 00 for minutes). If there is less than
100 hours the value is still not treated as [h]:mm. For example:

029:15

How can I erase the first one or two zeros??

stremetzky

 

[Rick Rothstein \(MVP - VB\)]

Not sure if this is any better. the real problem is sttripping off

leading
zeros

=TIMEVALUE(trimchar(E16,"0"))

wrote my own function to strip off leading zeroes

Function trimchar(MyString As String, MyChar As String) As String

trimchar = MyString
Do While Left(trimchar, 1) = MyChar
trimchar = Mid(trimchar, 2)
Loop
End Function

You can do this directly with worksheet formulas...

=TIMEVALUE(SUBSTITUTE(TRIM(SUBSTITUTE(C2,"0"," "))," ", "0"))

Rick

 

[David Biddulph]

That seems to return 5:15, not 29:15 ?

 

[Rick Rothstein \(MVP - VB\)]

I think it returns what Joel's formula returns, which is all I was trying to
do. HOWEVER, the formula I posted has a much bigger problem... it doesn't
handle trailing zeroes correctly... I would need a LTRIM function to do
that, but Excel does not have one. So, my entire post should be ignored.

Rick

 

[Harlan Grove]

Can you suggest an easier way of converting 029:15 from text to
time, Joel?
I feel there ought to be an easier way, but the usual ways don't
seem to work in this case.

....

=LOOKUP(1E6,--MID(A1,{3;2;1},16))

 

[Rick Rothstein \(MVP - VB\)]

That seems to return 5:15, not 29:15 ?

I think it returns what Joel's formula returns, which is all I was trying
to do. HOWEVER, the formula I posted has a much bigger problem... it
doesn't handle trailing zeroes correctly... I would need a LTRIM function
to do that, but Excel does not have one. So, my entire post should be
ignored.

Okay, first let me state that I am not entirely sure what final result the
OP was looking for; however, if all that is wanted is to return a text
string without leading zeroes (and which handles trailing zeroes
correctly<g>), then I think this does that...

=SUBSTITUTE(TEXT(SUBSTITUTE(C2,":","."),"#.00"),".",":")

Rick

 

[ryanpoth]

=LOOKUP(1E6,--MID(A1,{3;2;1},16))

Very elegant, Harlan.

 

[stremetzky]

I have got solution for it:

=LEFT(A1)/24*100+RIGHT(A1,5)

Thank you for help anyway,

stremetzky