word wrap in custom date formatting

[merlin777]

I've got this:

sat o1 Jan

but I want this:

sat
o1
Jan

to keep my columns narrow.

I'm using a custom date format but word wrap doesn't seem to work with this. Is there a newline code i can use in the custom date template instead?

 

[MickG]

This may be an option:-

Dim Rng As Range, Dn As Range
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
For Each Dn In Rng
    Dn = Replace(Dn, " ", Chr(10))
Next Dn
Rng.Rows.AutoFit

Mick

 

[Yahya]

May this will help

Sub TestIt()
Dim LR As Long, c As Range
LR = Range("A" & Rows.Count).End(xlUp).Row
For Each c In Range("A2:A" & LR)
    c.Value = Format(c.Value, "ddd" & Chr(10) & "yy" & Chr(10) & "mmm")
Next c
End Sub

but the date will convert to text

 

[jasonb75]

With a custom format the regular way, enter

ddd ALT+0010 dd ALT+0010 mmm

note that the codes entered with the ALT key must be done on the numberpad with NumLock on.

 

[merlin777]

I can't use vba because of the way we share workbooks. I was hoping the custom templates allowed for a split line?

 

[jasonb75]

There is no vba in #4

 

[merlin777]

There is no vba in #4

sorry - my reply crossed over with #4.

at first this didn't work with just the custom format but if you then apply wordwrap it works perfectly - thanks!