Format dates so they are split across two lines

0

enter image description here

See the dates above? I'd like to format them so they are split over two lines, as illustrated below (Note: the Oct 13 is a mistake; it should have been split as well).

In other words, between the month and the day I'd like either:

  • A soft line break, which makes the day wrap around to the second line only when the cell is too narrow
  • A hard line break, such that the day is always on the second line.

The date would preferably be preserved as a number, but if it has to be turned into a string, then so be it.

How do I do this?

enter image description here

For illustration purposes, I did it by hand by pasting into a Word document and rearranging cells. But I'm looking to do this natively in Excel.

I tried adding a newline character in the number format:

mmm \n dd
mmm ^p dd

but none of these did anything (aside from adding an unwanted n or p).

I, of course, also tried Wrap Text and reducing cell width, but that resulted in ####. Apparently, wrap text doesn't work on formatted numbers.

Jean-François Corbett

Posted 2014-09-17T11:42:54.430

Reputation: 2 219

Couple question for you; 1) You still want Excel to recognize it as a date or is this purely an aesthetic issue? 2) Have you tried wrap text and reducing the cell width? – CharlieRB – 2014-09-17T11:54:13.870

@CharlieRB is correct, if you want it recognized as a date you will need to do some wrap text and width adjustment. Otherwise, if it's text, there's plenty of things we could do. – Raystafarian – 2014-09-17T11:57:18.227

Thanks for answering my questions. I would suggest trying my answer below. It is the only way I know to easily retain the data as dates. The only thing you are doing is changing the format. – CharlieRB – 2014-09-17T12:29:00.290

Answers

0

One solution is to place the date in the first cell across the row formatted as mmm. Then add =A1 in A2 and so forth across the second row so they simply reflect the same as the cell above. Then format that row as dd. It ends up looking like this;

enter image description here

If is has to be in the same cell, you will have to use wrap text.

CharlieRB

Posted 2014-09-17T11:42:54.430

Reputation: 21 303

0

In the custom format use Alt + 0010 for a line break, although then you can't reduce the column width anyway. Still, it might come in useful.

Elizabeth

Posted 2014-09-17T11:42:54.430

Reputation: 1

Hmm, this does insert a line break in the custom format string, but this doesn't translate into a line break in the actually displayed format in the cell. – Jean-François Corbett – 2014-12-18T09:11:10.343