Excel, remove a special character from a date cell

August 30, 2023

I have a worksheet with a range of date cells however the values within the cells all have a "’" in front of the data. for example ’03/03/2023. ’04/08/2023 and so on

The apostrophe is throwing off my vlookups so I want to remove all of them however find and replace isn’t picking up the character?

I want to include the replace into my worksheet open event procedure

Can anyone help ?

>Solution :

You can use this sub:

Sub removeApostrophFromDate(ws As Worksheet)
    Dim c As Range
    For Each c In ws.UsedRange
        If IsDate(c.Value) Then
            c.Value = CDate(c.Value)
        End If
    Next
End Sub

It checks whether the cell-value can be interpreted as date and then returns the correct value.

You can call it like e.g. this: removeApostrophFromDate thisworkbook.worksheets("mySheet")