Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
working with cells and text
I have a number of cells that have duration of time values, i.e 6:30,5:28;
which are hours and minutes. I actually want these values but in text format. They would look identical but wouldn't have the Date/Time stamp on them. What I have done in the past is copy the column of data and paste into notepad, then made the column a text format then pasted back from notepad into Excel. This gives the desired result, however, there has to be a better way of doing this via code or something. Any help is appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
working with cells and text
Here is a procedure to change time values to text. The important thing to
note is that there is no IsTime function. Any value between 0 and .9999 could be displayed as a time. So this procedure takes those values and converts them to a corresponding time string (whether they are really a time or not). You can use it by highlighting the cells you want to convert (one cell, a group of cells, entire columns or rows) and executing the procedure. Private Sub Convert() Dim rngCurrent As Range Dim rngToSearch As Range Dim strTime As String Set rngToSearch = Intersect(ActiveSheet.UsedRange, Selection) If rngToSearch Is Nothing Then Set rngToSearch = ActiveCell If Not rngToSearch Is Nothing Then Application.Calculation = xlCalculationManual For Each rngCurrent In rngToSearch If Left(rngCurrent.Value, 1) < "=" Then If rngCurrent.Value < 1 And rngCurrent.Value 0 Then strTime = CStr(Format(rngCurrent.Value, "h:mm")) rngCurrent.NumberFormat = "@" rngCurrent.Value = strTime End If End If Next Application.Calculation = xlCalculationAutomatic End If End Sub -- HTH... Jim Thomlinson "Job" wrote: I have a number of cells that have duration of time values, i.e 6:30,5:28; which are hours and minutes. I actually want these values but in text format. They would look identical but wouldn't have the Date/Time stamp on them. What I have done in the past is copy the column of data and paste into notepad, then made the column a text format then pasted back from notepad into Excel. This gives the desired result, however, there has to be a better way of doing this via code or something. Any help is appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
working with cells and text
Job,
Select the cells with the times, and run the macro below. HTH, Bernie MS Excel MVP Sub test() Dim mycell As Range For Each mycell In Selection mycell.Value = "'" & Format(mycell.Value, "h:mm") Next mycell End Sub "Job" wrote in message ... I have a number of cells that have duration of time values, i.e 6:30,5:28; which are hours and minutes. I actually want these values but in text format. They would look identical but wouldn't have the Date/Time stamp on them. What I have done in the past is copy the column of data and paste into notepad, then made the column a text format then pasted back from notepad into Excel. This gives the desired result, however, there has to be a better way of doing this via code or something. Any help is appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
working with cells and text
Brilliant, thank you both. I ended up using Jims code as I didn't want the
"'" in the cell. Cheers, Job "Job" wrote in message ... I have a number of cells that have duration of time values, i.e 6:30,5:28; which are hours and minutes. I actually want these values but in text format. They would look identical but wouldn't have the Date/Time stamp on them. What I have done in the past is copy the column of data and paste into notepad, then made the column a text format then pasted back from notepad into Excel. This gives the desired result, however, there has to be a better way of doing this via code or something. Any help is appreciated. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
working with cells and text
Ok, I have a small snag. The value in the Cell is 83:15 which is 83 min
and 15 seconds. It's format is actually 83:15:00 as opposed to the others that are like 15:51. The values that are in this column are just minutes and seconds. Excel must be converting them as they are put into Excel, however, I only recieve the file after it has been saved as an .xls. Any thoughts? "Job" wrote in message ... I have a number of cells that have duration of time values, i.e 6:30,5:28; which are hours and minutes. I actually want these values but in text format. They would look identical but wouldn't have the Date/Time stamp on them. What I have done in the past is copy the column of data and paste into notepad, then made the column a text format then pasted back from notepad into Excel. This gives the desired result, however, there has to be a better way of doing this via code or something. Any help is appreciated. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
working with cells and text
I just noticed that in my first post I said it was hours and minutes...it's
actually minutes and seconds... "Job" wrote in message ... Ok, I have a small snag. The value in the Cell is 83:15 which is 83 min and 15 seconds. It's format is actually 83:15:00 as opposed to the others that are like 15:51. The values that are in this column are just minutes and seconds. Excel must be converting them as they are put into Excel, however, I only recieve the file after it has been saved as an .xls. Any thoughts? "Job" wrote in message ... I have a number of cells that have duration of time values, i.e 6:30,5:28; which are hours and minutes. I actually want these values but in text format. They would look identical but wouldn't have the Date/Time stamp on them. What I have done in the past is copy the column of data and paste into notepad, then made the column a text format then pasted back from notepad into Excel. This gives the desired result, however, there has to be a better way of doing this via code or something. Any help is appreciated. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
working with cells and text
Play with this and see if you can get what you want...
Private Sub Convert() Dim rngCurrent As Range Dim rngToSearch As Range Dim strTime As String Set rngToSearch = Intersect(ActiveSheet.UsedRange, Selection) If rngToSearch Is Nothing Then Set rngToSearch = ActiveCell If Not rngToSearch Is Nothing Then Application.Calculation = xlCalculationManual For Each rngCurrent In rngToSearch If Left(rngCurrent.Value, 1) < "=" And IsNumeric(rngCurrent.Value) Then If rngCurrent.Value < 1 And rngCurrent.Value 0 Then strTime = CStr(Format(rngCurrent.Value, "mm:ss")) rngCurrent.NumberFormat = "@" rngCurrent.Value = strTime ElseIf rngCurrent.Value 0 Then strTime = CStr(Format(rngCurrent.Value / 60, "h:mm:ss")) rngCurrent.NumberFormat = "@" rngCurrent.Value = strTime End If End If Next Application.Calculation = xlCalculationAutomatic End If End Sub -- HTH... Jim Thomlinson "Job" wrote: Ok, I have a small snag. The value in the Cell is 83:15 which is 83 min and 15 seconds. It's format is actually 83:15:00 as opposed to the others that are like 15:51. The values that are in this column are just minutes and seconds. Excel must be converting them as they are put into Excel, however, I only recieve the file after it has been saved as an .xls. Any thoughts? "Job" wrote in message ... I have a number of cells that have duration of time values, i.e 6:30,5:28; which are hours and minutes. I actually want these values but in text format. They would look identical but wouldn't have the Date/Time stamp on them. What I have done in the past is copy the column of data and paste into notepad, then made the column a text format then pasted back from notepad into Excel. This gives the desired result, however, there has to be a better way of doing this via code or something. Any help is appreciated. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
working with cells and text
Great. Thanks Jim. Looks like I can tweak this to fit my needs. Your help
is much appreciated. "Jim Thomlinson" wrote in message ... Play with this and see if you can get what you want... Private Sub Convert() Dim rngCurrent As Range Dim rngToSearch As Range Dim strTime As String Set rngToSearch = Intersect(ActiveSheet.UsedRange, Selection) If rngToSearch Is Nothing Then Set rngToSearch = ActiveCell If Not rngToSearch Is Nothing Then Application.Calculation = xlCalculationManual For Each rngCurrent In rngToSearch If Left(rngCurrent.Value, 1) < "=" And IsNumeric(rngCurrent.Value) Then If rngCurrent.Value < 1 And rngCurrent.Value 0 Then strTime = CStr(Format(rngCurrent.Value, "mm:ss")) rngCurrent.NumberFormat = "@" rngCurrent.Value = strTime ElseIf rngCurrent.Value 0 Then strTime = CStr(Format(rngCurrent.Value / 60, "h:mm:ss")) rngCurrent.NumberFormat = "@" rngCurrent.Value = strTime End If End If Next Application.Calculation = xlCalculationAutomatic End If End Sub -- HTH... Jim Thomlinson "Job" wrote: Ok, I have a small snag. The value in the Cell is 83:15 which is 83 min and 15 seconds. It's format is actually 83:15:00 as opposed to the others that are like 15:51. The values that are in this column are just minutes and seconds. Excel must be converting them as they are put into Excel, however, I only recieve the file after it has been saved as an .xls. Any thoughts? "Job" wrote in message ... I have a number of cells that have duration of time values, i.e 6:30,5:28; which are hours and minutes. I actually want these values but in text format. They would look identical but wouldn't have the Date/Time stamp on them. What I have done in the past is copy the column of data and paste into notepad, then made the column a text format then pasted back from notepad into Excel. This gives the desired result, however, there has to be a better way of doing this via code or something. Any help is appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text join formulas working but act as straight text when modified | New Users to Excel | |||
Working with Random Text & Combining Cells | Excel Discussion (Misc queries) | |||
Can I concatenate text in cells to make a working formula? | Excel Discussion (Misc queries) | |||
Computations involving text cells are not working?? | Excel Worksheet Functions | |||
Working with pictures and text in cells. | Excel Programming |