Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
job job is offline
external usenet poster
 
Posts: 65
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,119
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,441
Default 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   Report Post  
Posted to microsoft.public.excel.programming
job job is offline
external usenet poster
 
Posts: 65
Default 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   Report Post  
Posted to microsoft.public.excel.programming
job job is offline
external usenet poster
 
Posts: 65
Default 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   Report Post  
Posted to microsoft.public.excel.programming
job job is offline
external usenet poster
 
Posts: 65
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,119
Default 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   Report Post  
Posted to microsoft.public.excel.programming
job job is offline
external usenet poster
 
Posts: 65
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Text join formulas working but act as straight text when modified akkrug New Users to Excel 3 May 14th 08 02:27 PM
Working with Random Text & Combining Cells Louise Excel Discussion (Misc queries) 10 May 30th 06 11:58 PM
Can I concatenate text in cells to make a working formula? Matt S. R. Excel Discussion (Misc queries) 11 November 11th 05 04:44 PM
Computations involving text cells are not working?? Text Cell Computations Excel Worksheet Functions 2 January 31st 05 07:48 PM
Working with pictures and text in cells. Phillips[_4_] Excel Programming 1 January 26th 04 01:38 AM


All times are GMT +1. The time now is 04:30 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"