Problem with time format :

[danuk1976]

hi all

I have 2 cells

3:00pm
3:00

I want to get rid of the : from each cell and change it to .

I found/edited this code which works for 3:00pm but not 3:00?

Sub Remove()     
    Dim rgxRegExp As Object
    Dim rngCell As Range, rngRange As Range
    
    Dim abc As String 
    
    abc = "." 
    
    Set rngRange = Sheet1.Range("A1:A100")
     
    Set rgxRegExp = CreateObject("VBScript.RegExp")
    rgxRegExp.Global = True
    rgxRegExp.Pattern = ":" 'what we want to replace
     
    With Application
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
    End With
     
    For Each rngCell In rngRange.SpecialCells(xlCellTypeVisible)
        rngCell.Value = rgxRegExp.Replace(rngCell.Value, abc) 
    Next
     
    With Application
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
        .ScreenUpdating = True
    End With
     
End Sub

Also if I want to add the word "from" to the start of each cell using code below it works for 3:00pm but turns 3:00 to a fraction of a number

Sub AppendToExistingOnLeft()
Columns ("a:a").select
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = "From " & c.Value
Next
End Sub

Any help appreciated

 

[SpillerBD]

"3:00pm" will be a Text value in Excel
"3:00" & "3:00 PM" Will be Numbers displayed as Time.
The difference between a Text value and a Number are the reason you get different results.

 

[James006]

Hello,

You have to go back to the basics ....

3 possibilities :

First 3.00pm is considered as Text ... so any string manipulation will work fine ..
Second 3:00 is considered as Number ... a number is a display ... not what is actually stored in Excel ...
Third 3:00 PM is considered as a Number ... note the space between last 0 and PM ...

In the last two cases ... you cannot manipulate the strings ...

Hope this explanation is clear enough ...

 

[danuk1976]

"3:00pm" will be a Text value in Excel
"3:00" & "3:00 PM" Will be Numbers displayed as Time.
The difference between a Text value and a Number are the reason you get different results.

I had been trying to format the column to text after the data was already in the column, with no luck

the minute i read your message I set the column to text prior to entering the data then it worked, so thanks for the nudge

 

[SpillerBD]

You might be able to use Text-to-Columns after and make sure the target data is set to Text.