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

    split value over 2 lines (2003)

    Hi All,

    I have a date like 12/23/2005 (US format). Using the date formating, I can have this show as "Friday, 12/23". Using custom formatting, I can even show this as "Fri 12/23". But what I'd like to do is have "Fri" on one line and "12/23" on a second line in the same cell.

    I was looking at <post#=377111>post 377111</post#> from macropod that seemed to allow 2 numbers to be separated by a line return within a single cell by using a custom format. I tried to use that for a custom format for my single date but had no luck - I can't seem to enter the ALT010 correctly.

    I can get something similar to what I want by doing something like:

    =TEXT(B4,"ddd") & CHAR(10) & TEXT(B4,"m/d"); or
    =TEXT(B4,"ddd" & " " & CHAR(10) & "m/d")

    where my original date is in cell B4.

    But what I want is to not have to create another cell; rather I'm looking for a way to do a line return within the custom format. I thought I saw another post about this by can't find it right now.

    TIA

    Fred

  2. #2
    Star Lounger
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: split value over 2 lines (2003)

    Hi Fred,

    for me

    =TEXT(B4,"ddd")&" "&TEXT(B4, "mm/dd")

    works.

    You need to format the cell alignment to "Wrap text" and adjust the number of spaces in the formula accordingly.

    There should be more spaces in the middle of the formula, but this program is automatically deleting double spaces I guess.
    (See the attached file)

    Wolf

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: split value over 2 lines (2003)

    < I tried to use that for a custom format for my single date but had no luck - I can't seem to enter the ALT010 correctly >

    Try entering the linefeed character (CHAR(10)) as ALT 0010, but wrap it in quotation marks (""). You will also need to set alignment property Word Wrap to on. You may have to manually set the row height to get the desired display. Also note that in the custom format dialog box, the format may not show correctly (omitting the linefeed and subsequent characters), but it should work ok. It will display as [b]ddd "

  4. #4
    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: split value over 2 lines (2003)

    Andrew indicates how this can be done, but there is one "flaw" (excel limitation) with it:
    If your goal is the have narrower columns since it is wrapped, this will not work.

    Custom formats require that the Length of the formatted string must fit into the complete column width. The "testing" it does, before it displays "#####..." does not check to see if it is wrapped.

    You can use custom formatting to display over multiple lines:
    Saturday
    Dec 24, 2005
    7:00 AM

    But the cell must be wide enough to display it like:
    Saturday Dec 24, 2005 7:00 AM

    Or it will display all "pounds" (#####) indicating the col width is too narrow for the formatting.

    Using TEXT function will allow narrowing the column but eliminates the value.

    Steve

  5. #5
    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: split value over 2 lines (2003)

    Thanks Wolf. The formulas in my original post worked but I was looking for a custom format so that I didn't have to use extra cells for the original dates.

    I looked at your attachment. I noticed you had the cell, which was shown on 2 lines, using a custom format. It didn't seem that was necessary - given your formula, it also seems like it could have been formatted as just text. So why use the custom format?

    Fred

  6. #6
    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: split value over 2 lines (2003)

    Andrew,

    My problem is that I can't seem to enter the ALT 010 or 0010 correctly. When I try to enter it from my laptop in the Type box of the Custom Format, I get a "ding" because something thinks I'm entering an illegal character. I'm using the numeric keypad for the 010 (on my laptop, I need to use the Fn key in conjunction with the ALT key since the "numeric keypad" does double duty with the "m", "j" etc keys BUT I also tried it on a desktop keypad and it didn't work either). Even using the number keys on the top row does no good. So herein is the problem I'm having.

    Also, given Steve's post about width (which rang a bell - pun intended), the custom format approach won't work. I can't afford the width. I think I'll have to go with a hidden row of dates and use one of my formulas per the original post.

    Thanks.

    Fred

  7. #7
    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: split value over 2 lines (2003)

    Thanks Steve.

    I think I'll have to go with the Text approach since I can't afford the col width. Now that you mention it, I do recall an old post saying that (although not the one I saved).

    But I still don't see how you got the line returns into the custom format. Per my response to Andrew, that is my problem if I were to go that route.

    Fred

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: split value over 2 lines (2003)

    Laptop keyboards sometimes make it difficult to enter the Alt/0010. If you can't figure out how to get your keyboard to do it, here is another possible approach. Select an empty cell and enter the formula:

    <code>
    =CHAR(10)
    </code>

    Select the cell and copy it. Now do a Paste Special and paste values back into the cell. This should give you a cell with the Alt/0010 in it. Now select the cell and in the formula bar (which should show as two empty lines) select both lines and do a copy. You should now be able to paste the Alt/0010 into your format.
    Legare Coleman

  9. #9
    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: split value over 2 lines (2003)

    On my laptop it works by holding both <alt><Fn> and entering "mjm".

    The alt combinations only work with a numeric keypad, The have never worked with the row at the top.

    You must also explicitly tell XL to wrap the cell (format - cells - alignment (tab) check "Wrap text"). You will also have to manually widen the row as this will not do it automatically.

    Steve

  10. #10
    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: split value over 2 lines (2003)

    Steve, Legare,

    Thanks for the suggestions but nothing seems to work with this keyboard.

    I know that for ALT combinations that you don't use the "regular" numbers - I just tried that since the "keypad" approach wasn't working. Even though I was now able to enter ALT+Fn+mjm without a ding, the info still didn't appear on 2 lines even with wrap text checked.

    I also tried the =CHAR(10) approach of Legare's but that didn't seem to do the trick with the custom format.

    I also just realized that I can't go the text way per my original approach because somewhere else I have a formula
    =COUNTIF(B10:AE10,"<=" & TODAY())
    to count the number of days of classes so far in the semester (the spreadsheet is a "grade book" and B10:AE10 contains the dates on which the class meets).

    So what I'm going to do is just insert another row above Row 10 to put in the weekday, especially given Steve's first response (col has to be wide enough to contain the entire day-date combination even though the cell is wrapped). I had forgotten that.

    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
  •