# How to extract the exact digits 1234 from 1234:00:00 in Excel

I have a series of IDs that are represented in a column with the [h]:mm:ss format in excel. I want to extract the [h] portion that precedes the colon and have it stored as a number. The problem I run into is that it converts the digits to a new number, not the digits as displayed.

Example: 1234:00:00 formatted as [h]:mm:ss Desired text in new column: 1234

I am looking to use a straight formula to solve.

## migrated from stackoverflow.comMay 29 '13 at 17:43

This question came from our site for professional and enthusiast programmers.

• What language? What is "it" (that converts the digits to a new number)? – emd May 29 '13 at 16:07
• If I convert the 1234:00:00 to a Number, the cell becomes 51.42. I am able to display 1234 if I format only as [h] but that is not searchable when trying to compare columns. I can't figure out how to both display 1234 in the cell as well as it actually be 1234, the number. – user2433269 May 29 '13 at 16:10

You have two options here.

If you want to simply display the hours, you can just copy and paste the cells (or a simple `=B5` formula) and give it a custom display format of `[h]`.

If you need to use the number for other calculations, then you can use the following formula:

``````=TRUNC(B5*24,0)
``````

And if you want to get a little fancier, using excel date/time methods, you can use:

``````=DATEDIF(0, B5,"D") * 24 + HOUR(B5)
``````
• That does the trick. You just saved me hours. Thank you. – user2433269 May 29 '13 at 16:20
• +1 for TRUNC formula (you could also use INT) - don't use DAYS360 here it doesn't give an accurate day count so, for example, it will convert 8888:00:00 to 8768 – barry houdini May 29 '13 at 23:26
• @barryhoudini: Thanks, I didn't realize that there was an issue with DAYS360 (I thought that's what it was for, lol). Second example removed. – lnafziger May 30 '13 at 0:08
• DAYS360 treats each year as if it has 360 days, i.e. 30 each month so it may not be accurate across months - in Excel 2013 there is a new DAYS function which does give an accurate count, in earlier versions you can use DATEDIF or if no times are involved just a simple subtraction. – barry houdini May 30 '13 at 10:37

Things like `=HOUR()` will not do the trick since it will not include bits that spill over into day periods. Ditto `=TEXT(A1, "hh") * 1`. 1234 hours spills over into a number of days.

You need to do this:

``````=ROUNDDOWN(VALUE(D5)*24,0)
``````

Which will include whole days in the total number of hours. Rounding strips the minutes etc.

This works because Excel represents time as numbers which increase by 1 each day.

The VALUE call circumvents Excel's nasty habit of propagating time formats when editing untouched cells. You may not need it in later Excel versions.

• +1 Yup, I would have originally gone for copy/paste to a txt then import as txt file, but the habit of using `text()` has been growing on me for the past few weeks, heh xD. Using notepad for assistance is still a favourite of me! – Jerry May 29 '13 at 16:30
• TEXT function will work if you use it like this `=TEXT(A1,"[h]")*1` - format result cell as general – barry houdini May 29 '13 at 23:23

If you're working in a sheet formula, use the `hour` function: If you're using VBA, similar solution:

``````MsgBox Hour(sheet.Cells(1, 1))
``````
• That works if the hour is actually an hour but doesn't with the numbers I'm using, which are actually IDs. 1234:00:00 with the formula above gives the output of 10, not 1234. :-/ – user2433269 May 29 '13 at 16:15

If your numbers were stored as text instead of times, you could have used this simple array formula to extract the first continuous numeric sequence from the text:

``````=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW(\$1:\$14),1)),0),MATCH(FALSE,ISNUMBER(1*MID(A1,ROW(OFFSET(\$1:\$14,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW(\$1:\$14),1)),0)-1,)),1)),0)-1)
``````

note: press CTRl+SHIFT+ENTER when entering as it is an array formula