Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reformat existing 4 digit number as hh:mm?
I have a column of 3 and 4 digit numbers. I would like to insert a colon :
to the immediate left of the 2 numbers on the far right of the cell. Is there a way to do this easily? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reformat existing 4 digit number as hh:mm?
Carol if i read you correctly you need timeserials add a temporary column.. if the last 2 digits are seconds then with formula =TIMEVALUE(TEXT(B1,"00\:00\:00")) if they are minutes then with formula =TIMEVALUE(TEXT(B1,"00\:00")) dragdown.. then copy /pastespecial values.. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Carol Wolfe wrote : I have a column of 3 and 4 digit numbers. I would like to insert a colon : to the immediate left of the 2 numbers on the far right of the cell. Is there a way to do this easily? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reformat existing 4 digit number as hh:mm?
That didn't seem to work, here's a sample of what happened:
840 0.361111111 1213 I copied your formula into the cell in my temporary column "keepITcool" wrote: Carol if i read you correctly you need timeserials add a temporary column.. if the last 2 digits are seconds then with formula =TIMEVALUE(TEXT(B1,"00\:00\:00")) if they are minutes then with formula =TIMEVALUE(TEXT(B1,"00\:00")) dragdown.. then copy /pastespecial values.. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Carol Wolfe wrote : I have a column of 3 and 4 digit numbers. I would like to insert a colon : to the immediate left of the 2 numbers on the far right of the cell. Is there a way to do this easily? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reformat existing 4 digit number as hh:mm?
..3611 is the correct time serial.. you only need to format the new column to display the number as time. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Carol Wolfe wrote : That didn't seem to work, here's a sample of what happened: 840 0.361111111 1213 I copied your formula into the cell in my temporary column "keepITcool" wrote: Carol if i read you correctly you need timeserials add a temporary column.. if the last 2 digits are seconds then with formula =TIMEVALUE(TEXT(B1,"00\:00\:00")) if they are minutes then with formula =TIMEVALUE(TEXT(B1,"00\:00")) dragdown.. then copy /pastespecial values.. -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam Carol Wolfe wrote : I have a column of 3 and 4 digit numbers. I would like to insert a colon : to the immediate left of the 2 numbers on the far right of the cell. Is there a way to do this easily? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reformat existing 4 digit number as hh:mm?
If you don't want it to be an actual time that you can manipulate
mathematically then you can just use text functions =IF(LEN(A2)=3, LEFT(A2, 1) & ":" & RIGHT(A2,2), LEFT(A2,2) & ":" & RIGHT(A2,2)) Where the number is in Cell A2. Cools function (without having tried it) is giving you the time, but it needs to be re-foramtted. Select the cell with the formula and change the format from general to Time... It should then look correct... HTH "Carol Wolfe" wrote: I have a column of 3 and 4 digit numbers. I would like to insert a colon : to the immediate left of the 2 numbers on the far right of the cell. Is there a way to do this easily? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reformat existing 4 digit number as hh:mm?
spreadsheet
if C6 is your number, then in another cell =SUBSTITUTE(C6,RIGHT(C6,2),":" & RIGHT(C6,2)) VBA Sub Adjust() Dim cell As Range Dim text As String For Each cell In Range("A:A").SpecialCells(xlCellTypeConstants) text = cell.Value cell.Value = Replace(text, Right(text, 2), ":" & Right(text, 2)) Next End Sub "Carol Wolfe" wrote: I have a column of 3 and 4 digit numbers. I would like to insert a colon : to the immediate left of the 2 numbers on the far right of the cell. Is there a way to do this easily? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I reformat a 6-digit date to general & look the same? | Excel Discussion (Misc queries) | |||
How to validate a cell to insert from 9 digit number to 13 digit number. | Excel Worksheet Functions | |||
How to validate a cell to insert from 9 digit number to 13 digit number. | Excel Worksheet Functions | |||
Color a single digit in a mult-digit number cell | Excel Discussion (Misc queries) | |||
When we enter a 16 digit number (credit card) the last digit chan. | Excel Discussion (Misc queries) |