Carriage return in Chart Axis custom format

[wildpark]

Hi all,

I have a chart with a custom date format for the horizontal axis, inherited from someone else. The custom date format is mmm[]yyy (where the [] is actually a square box, indicating a non displayable character.

The effect of that non-displayable character is to push a carriage return into the chart axis, so that the month displays on a different line to the year.

This is all fine, but I have no idea what that character is, or how I can enter it into another worksheet.

Any ideas? I'm on Excel 2007.

I've tried copying the custom format, but in the Format Code box it simply shows mmm and that's all I can copy.
I can't type Alt-Enter into the Format Code box.
I can't type Alt-020 there.

 

[njimack]

I do something similar, but not by formatting the axis. Instead, I have a range of cells that link to the dates, with a formula that uses Alt+Enter, like:

=TEXT(A1,"mmm
yyyy")

I then use this range as the x-axis in the chart.

 

[wildpark]

Thank you. Unfortunately, the nature of the worksheets means that any suggestion of adding another column probably wouldn't go down too well.

The frustrating thing is that there must be an answer, because the sheet I have does it - I just don't know how to replicate it!

 

[njimack]

Can't you copy the existing chart and link it to your new data?

 

[wildpark]

Can't you copy the existing chart and link it to your new data?

Oh, absolutely. It's just the annoyance that someone else has worked this out and I'd quite like to know how.

 

[shg]

Try Ctrl+J

 

[wildpark]

Try Ctrl+J

You're a star - that works perfectly.

Thank you.

 

[Razorboy]

Excel 2016 won't accept CTRL-J, but I found a VBA work around.

1. Open the VBA Editor (CTRL+F11)
2. Open the Immediate Window (CTRL-G) if it's not already visible (look for "Immediate" in a title bar)
3. Select your Chart (or some element of it, like the axis) back in the main Excel window
4. In the Immediate window, type (note not case-sensitive) "Debug.print ActiveChart.Axes(xlCategory,xlPrimary).TickLabels.NumberFormat"
5. The current X-Axis number format will be "printed" below the line in the Immediate window.
5. If the resulting string was "mm/dd/yyyy hh:mm:ss AM/PM" (like mine), insert a "vbLf" in the middle, wherever you want the line feed, removing any extra spaces that are being supplanted by the line feed. In my case, like this:
ActiveChart.Axes(xlCategory,xlPrimary).TickLabels.NumberFormat = "mm/dd/yyyy" & vbLf & "hh:mm:ss AM/PM"