Adjusting data in cell

[AlexanderBB]

I formatted a column to custom 'dd mmm yyyy' which is mostly what's wanted but some of the data has year only.
So I've been entering 'dd mmm 1999'. Now I don't like it as there's too many, and having the year only is tidier.
Changing the Format to text allows the year only but changes the full formatted Date to a number.
Some values also don't have a year so "07 Apr 19?" is used.
Is there a format where I can have all those without (as someone said) having a leading apostrophe?
And I think I need to remove the value for each cell, reformat it, and reapply the value.

 

[Scott Huish]

If that is correct, then this macro will change Column A ( change if necessary) to what I described above.

Sub test()
Dim c As Range, s
For Each c In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
If IsDate(c) And c <> "" Then
    c.NumberFormat = "dd mmm yyyy"
Else
    s = Split(c)
    If s(2) = "??" Then
        c = StrConv(s(1), vbProperCase)
    Else
        c = s(2)
    End If
End If
Next
End Sub

 

[AlexanderBB]

Scott, Col B would be dd mmm yyyy or however much is available from Col A.
Alex, is there a Text to column button ? The only way I know is to r-Click column, Then Format Cells - Number - Text. And I think it only works if the column (cell?) is empty.
I made the mistake of formatting Column to Custom "dd mmm yyyy" thinking it would leave anything it couldn't format as it was.

 

[Alex Blakenburg]

Alex, is there a Text to column button ?

There certainly is

You will find more information here >

7 Amazing Things Excel Text to Columns Can Do For You

In this tutorial, you will learn how to use Excel Text to Columns feature. It also covers 7 different cases where text to columns can be used.

trumpexcel.com

Converting Text to Numbers or Converting Dates is more common useage but in this case we want to convert the displayed value to Text.

 

[Joe4]

A "just put what I type in" category would be really useful.

It already exists - that is the "Text" option. If you format the column to cells to "Text" before you make your entries, it will always enter exactly what you type in with no conversions.
Of course, if you use that, you are then entering text entries and not date entries.