Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested formula to search a text string and return specific text | Excel Worksheet Functions | |||
extact text string from specific cell except three last characters | Excel Worksheet Functions | |||
How do I remove all text in a cell after a specific character? | Excel Worksheet Functions | |||
want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions | |||
want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions |