Read as text from a data dump

[DayDay]

Hi all,

I am having a problem with a formula which I would like to read a cell as text.

The dump results in a cell which reads hours and minutes like this - 199:30:00

199 hours, 30 mins, 0 secs

I want the formula to read this as "199:30:00" but when I reference the cell it reads it as 8.3125 and if I click on the cell once dumped it reads as 1/8/1900 7:30:00 AM

My goal is to use the FIND command to fin the ':' and take the hours and minutes from that reference within the cell.

I have tried changing the formats of the cells once dumped to be text, general, and even H:MM:SS or HHH:MM:SS but nothing seems to work!

Please help me

 

[Jonmo1]

Reference it using the text command

=TEXT(A1,"[h]:mm:ss")

Hope that helps...

 

[iliace]

You want [h]:mm for the time format.

If you do not want Excel to interpret time as a serial number, put an apostrophe in front of it. So, if you enter:

'199:30:15

The ' will not be displayed (except in the formula bar) and Excel will treat it as text.

To get the hours value from text, you would use:

=LEFT(A1, FIND(":",A1)-1)

assuming the time is in A1.

 

[DayDay]

Thank you very much J, why did I not think?

The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

You're sig line could not be any more appropriate.

Thanks again