Convert displayed date to mm/yyyy format, and convert that to text

metertreater 

The format doesn't really matter - a date is a date regardless of how it is displayed.

So it is sufficient to convert the values to 'real' dates. After importing them into Access, apply the custom format mm/yyyy there.

Here is a macro. Select the range with the values to be converted before you run the macro.

Sub Num2Date()
    Dim rng As Range
    Dim s As String
    Application.ScreenUpdating = False
    For Each rng In Selection
        s = rng.Value
        rng = DateSerial(Right(s, 4), Left(s, Len(s) - 4), 1)
    Next rng
    Selection.EntireColumn.AutoFit
    Application.ScreenUpdating = True
End Sub