Spaces in Year

[rodsin76]

Hi,

Can you help me on this?

Date	Result
05-29-2024	2 0 2 4
I want the year have space every number.

Moderator note: Split from Asterisk in Spellnumber, as this is an entirely new question.

 

[Joe4]

Is that date actually entered as a date with special formatting, or is it being entered as text?

 

[rodsin76]

Is that date actually entered as a date with special formatting, or is it being entered as text?

The date is entered as date format...but i want the year have space between number (another command maybe on that matter)

 

[Rick Rothstein]

It appears you cannot do that using a Custom Format since Excel automatically changes any single y to yy, so you cannot insert spaces between the y's in the formatter. Do your dates have to remain as real dates? If not, can the formatted date be in a different cell than the real date?

 

[Joe4]

Not the most graceful solution, but it works.
If you have a date in A1, this will formula will return just the year with spaces between each of the numbers:

=LEFT(YEAR(A1),1)&" "&MID(YEAR(A1),2,1)&" "&MID(YEAR(A1),3,1)&" "&RIGHT(YEAR(A1),1)