Remove specific text from cell string
 

Im trying to run a macro to look at imported time data in a cell,
remove the am/pm designation and convert the time to 24 clock.

For now I will just select the range of data and run the macro...
the imported data is in general format example: "953am"

My though is to run through the column (selection) test each cell
value and if the cell contains the string "pm", remove "pm" and add
1200 to the numeric value.

If the cell contains "am" then just remove am.

just to get findingt he string right I started with:

Sub FixTime()
Dim timeCell As Range

For Each timeCell In Selection
If timeCell.Value = Right("pm", 2) Then
timeCell.Value = timeCell.Replace("pm", "")
End If

Next
End Sub

However, all the cells with "pm" in them get bypassed
any help will be appreciated.

Robert

Remove specific text from cell string
 

Hello Robert
For Each timeCell In Selection
If Right(timeCell.Value, 2) = "pm" Then
timeCell.Replace "pm", ""
End If
Next

HTH
Cordially
Pascal

"Robert H" a écrit dans le message de news:
...
Im trying to run a macro to look at imported time data in a cell,
remove the am/pm designation and convert the time to 24 clock.

For now I will just select the range of data and run the macro...
the imported data is in general format example: "953am"

My though is to run through the column (selection) test each cell
value and if the cell contains the string "pm", remove "pm" and add
1200 to the numeric value.

If the cell contains "am" then just remove am.

just to get findingt he string right I started with:

Sub FixTime()
Dim timeCell As Range

For Each timeCell In Selection
If timeCell.Value = Right("pm", 2) Then
timeCell.Value = timeCell.Replace("pm", "")
End If

Next
End Sub

However, all the cells with "pm" in them get bypassed
any help will be appreciated.

Robert

Remove specific text from cell string
 

Hi Robert,

If the imported values are text values, perhaps try
something like:

'=============
Public Sub Tester()
Dim timeCell As Range
Dim Rng As Range

Set Rng = Selection
For Each timeCell In Rng.Cells
With timeCell
.Value = TimeValue(.Value)
End With
Next timeCell

Rng.NumberFormat = "hh:mm"

End Sub
'<<=============

---
Regards,
Norman


"Robert H" wrote in message
ups.com...
Im trying to run a macro to look at imported time data in a cell,
remove the am/pm designation and convert the time to 24 clock.

For now I will just select the range of data and run the macro...
the imported data is in general format example: "953am"

My though is to run through the column (selection) test each cell
value and if the cell contains the string "pm", remove "pm" and add
1200 to the numeric value.

If the cell contains "am" then just remove am.

just to get findingt he string right I started with:

Sub FixTime()
Dim timeCell As Range

For Each timeCell In Selection
If timeCell.Value = Right("pm", 2) Then
timeCell.Value = timeCell.Replace("pm", "")
End If

Next
End Sub

However, all the cells with "pm" in them get bypassed
any help will be appreciated.

Robert

Remove specific text from cell string
 

If the text is exactly as you show "953pm", then this will add the : and
space where needed and convert to 24 hour format:

Sub FixTime()
Dim timeCell As Range

For Each timeCell In Selection
If Right(timeCell, 2) = "pm" Or Right(timeCell, 2) = "am" Then
timeCell.Value = Left(timeCell, Len(timeCell) - 4) & ":" & _
Mid(timeCell, Len(timeCell) - 3, 2) & " " & Right(timeCell, 2)
timeCell.NumberFormat = "hh:mm"
End If
Next
End Sub

Mike F
"Robert H" wrote in message
ups.com...
Im trying to run a macro to look at imported time data in a cell,
remove the am/pm designation and convert the time to 24 clock.

For now I will just select the range of data and run the macro...
the imported data is in general format example: "953am"

My though is to run through the column (selection) test each cell
value and if the cell contains the string "pm", remove "pm" and add
1200 to the numeric value.

If the cell contains "am" then just remove am.

just to get findingt he string right I started with:

Sub FixTime()
Dim timeCell As Range

For Each timeCell In Selection
If timeCell.Value = Right("pm", 2) Then
timeCell.Value = timeCell.Replace("pm", "")
End If

Next
End Sub

However, all the cells with "pm" in them get bypassed
any help will be appreciated.

Robert

Remove specific text from cell string
 

I used:

Sub FixTime()
Dim timeCell As Range

For Each timeCell In Selection
If Right(timeCell, 2) = "pm" Or Right(timeCell, 2) = "am" Then
timeCell.Value = Left(timeCell, Len(timeCell) - 4) & ":" & _
Mid(timeCell, Len(timeCell) - 3, 2) & " " & Right(timeCell, 2)
timeCell.NumberFormat = "hh:mm"
End If
Next
End Sub

and it worked perfect. Thaks Mike and all for posting.
V/R
Robert

Remove specific text from cell string
 

That's great! However I have one more question. Will you have to deal with
times between midnight and 1:00 AM? I have found in my experience that the
leading 0's are truncated off. 9 minutes after midnight may import as "9pm",
45 minutes would be "45pm", etc. The code I gave you only works on string
lengths of 5 or greater ("100am"). More code is needed to check if the
string length is <5 and deal with the possibility that it is 3 or 4
characters in length.
If your situation fits this scenario, then post back with an example of
those times.

Mike F
"Robert H" wrote in message
ups.com...
I used:

Sub FixTime()
Dim timeCell As Range

For Each timeCell In Selection
If Right(timeCell, 2) = "pm" Or Right(timeCell, 2) = "am" Then
timeCell.Value = Left(timeCell, Len(timeCell) - 4) & ":" & _
Mid(timeCell, Len(timeCell) - 3, 2) & " " & Right(timeCell, 2)
timeCell.NumberFormat = "hh:mm"
End If
Next
End Sub

and it worked perfect. Thaks Mike and all for posting.
V/R
Robert

Remove specific text from cell string
 

Sorry, my example says "9pm" & "45pm" where I meant "am".

Mike F
"Mike Fogleman" wrote in message
m...
That's great! However I have one more question. Will you have to deal with
times between midnight and 1:00 AM? I have found in my experience that the
leading 0's are truncated off. 9 minutes after midnight may import as
"9pm", 45 minutes would be "45pm", etc. The code I gave you only works on
string lengths of 5 or greater ("100am"). More code is needed to check if
the string length is <5 and deal with the possibility that it is 3 or 4
characters in length.
If your situation fits this scenario, then post back with an example of
those times.

Mike F
"Robert H" wrote in message
ups.com...
I used:

Sub FixTime()
Dim timeCell As Range

For Each timeCell In Selection
If Right(timeCell, 2) = "pm" Or Right(timeCell, 2) = "am" Then
timeCell.Value = Left(timeCell, Len(timeCell) - 4) & ":" & _
Mid(timeCell, Len(timeCell) - 3, 2) & " " & Right(timeCell, 2)
timeCell.NumberFormat = "hh:mm"
End If
Next
End Sub

and it worked perfect. Thaks Mike and all for posting.
V/R
Robert