Paste Special problem

[terrypin]

I'm sure this should be trivially easy but so far I'm stumped.

There's a date in B6. I've copied it, selected B23, then used Paste Special > Values. I assumed that would give me plain text. So that I could quickly change it to Friday 18th November 2016. But as you see, it's still shown at the top as 18/11/2016. How do I achieve this without retyping the cell completely please? (I have many similar.)

 

[Sektor]

You need to set custom number format: MMMM dd"th" yyyy

 

[Joe4]

You need to set custom number format: MMMM dd"th" yyyy

That would only work for certain dates, and not dates like:
1st
2nd
3rd

So I think it will require a more complex solution like this:
Ordinal Suffixes In Excel
or this:
Ordinal Numbers Suffixes

 

[Sektor]

@Joe
Good catch. Yes, those suffixes - I didn't pay attention. Then, yes, more complex solution must be involved.

 

[terrypin]

Thanks both. But I'm hoping that I don't have to resort to either a complex formula or conditional formatting Joe. I'm quite happy to simply type the ordinal - if only I can get the cell in shape to do that! Is there really no way to get from the date in B6 to its text-editable form in B23?

--
Terry, East Grinstead, UK

 

[Joe4]

The issue is that dates are really stored as whole numbers, specifically the number of days since 1 January 1900.
The format just changes the way it is displayed, not its underlying value. So Copy | Paste Special | Values does not work on that.
You would need to change it to Text first, like this:
=TEXT(B6,"dddd dd mmmm yyyy")
Then, if you do Copy | Paste Special | Values on the cell with that formula, it will do what you want (without the st, rd, th).

 

[terrypin]

The issue is that dates are really stored as whole numbers, specifically the number of days since 1 January 1900.
The format just changes the way it is displayed, not its underlying value. So Copy | Paste Special | Values does not work on that.
You would need to change it to Text first, like this:
=TEXT(B6,"dddd dd mmmm yyyy")
Then, if you do Copy | Paste Special | Values on the cell with that formula, it will do what you want (without the st, rd, th).

Thanks, I'll try that first thing tomorrow morning.