Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Chart Axis Custom Format (XL 97)

    I have an automated charting tool that uses the custom format "m/d/yy h:mm" to format the X-Axis when it is date/time. It usually looks fine; it is displayed as two lines, date then time; however, if there are just a few points (<5), then XL decides that there is plenty of room and scrunches it all on one line. Is there anyway to insert a LF/CR in a custom format string, something like [NEWLINE] or 012. Any ideas? TIA --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Chart Axis Custom Format (XL 97)

    You can set the format to "m/d/yy" & vbLf & "h:mm". If you do this interactively, you'd have to type Alt+010 (on the numeric keypad) in the custom format.

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Chart Axis Custom Format (XL 97)

    I've gotten sidetracked on other things, but that looks like exactly what I need. I'll try it hopefully next week. Thanks, Hans!
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Chart Axis Custom Format (XL 97)

    Hans,

    Does your approach only work with charts? Not having a chart at hand (and seeing utility to this for non-chart applications), I tried applying this to
    - a custom format for a cell with a date entry
    - the format specification in the 2nd arg in the text function

    I was trying to keep it simple just to see if it would work. So I had a cell with today's date and tried to concatenate the cell value to vbLF and "hello" just to see what would happen. For example, something like
    =text(a1,"dd mm yy" & Alt+010 & "hello") where a1 has a date and Alt+010 was entered on the numeric keypad.

    The date part came out ok but the rest of the entry was messed up. Since the laptop was "bonging" as I entered the Alt+010, I'm betting I did something wrong there.

    Fred

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Chart Axis Custom Format (XL 97)

    You can't use Alt+010 in a formula. Try this:

    =TEXT(A1,"dd-mm-yy"&CHAR(10)&"hello")

    CHAR(10) is the formula equivalent ov Alt+010 and of the VBA expression vbLf = Chr(10). The backslashes in "hello" indicate that h and e are literal characters instead of formatting codes (h=hour, e=exponent).

    Oh, and you'll have to specify Wrap Text for the cell containing the formula (in the Alignment tab of Format | Cells).

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Chart Axis Custom Format (XL 97)

    You can use it in a custom format, but there is a "serious" limitation. The col width must be wide enough to show the entire entry "unwrapped" or excel will display the "###############" indicating the col is too narrow.

    In my mind, this defeats the purpose of adding it to the custom format...

    Steve

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Chart Axis Custom Format (XL 97)

    Good point, forgot to mention that (it has come up here before).

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Chart Axis Custom Format (XL 97)

    Hi Hans and Steve,

    The ...&CHAR(10)&... and the slashes made things better in that I did not get "garbage" where I had the h and e (bad choice of word in this case). But you also have to have wrap checked or the CHAR(10) is ignored. So the TEXT approach seems to work.

    As Steve pointed out though, the custom format approach is inviting but doesn't really do what I'd want.

    Thanks.

    Fred

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •