Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Insert CR/LF In Text? (Excel 97-SR2)

    Hi Folks

    Anyone found a way to insert a CR/LF into a cell/TEXT(string) without resorting to VBA?

    Regards
    Peter

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

    Re: Insert CR/LF In Text? (Excel 97-SR2)

    Alt+Enter

  3. #3
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert CR/LF In Text? (Excel 97-SR2)

    Hi Hans

    Right answer, thank you, I asked the wrong question! Specifically what I am trying to do is:-

    Assume Cell A1 = "First Line" and Cell B1 = "Second Line"
    =A1&CR/LF&B1

    i.e. I want to enter it within a formula. I then ASS-U-ME(!) that if I format the cell(s) to wrap text, I should get something like:-

    First Line
    Second Line

    Do I make any sense?

    Regards
    Peter

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

    Re: Insert CR/LF In Text? (Excel 97-SR2)

    Hi Peter,

    I don't know the reasoning behind it, but you only need a LF (character 10) instead of CR/LF:

    =A1&CHAR(10)&B1

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Insert CR/LF In Text? (Excel 97-SR2)

    Hi Peter,
    =A1&CHAR(10)&B1 works for me in XL2K2 - I don't have XL97 to test it on.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert CR/LF In Text? (Excel 97-SR2)

    Another option:

    =A1 & "Alt-Enter" & B1

    I mean pressing the keys Alt and enter of course...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert CR/LF In Text? (Excel 97-SR2)

    Hi Folks

    =A1&CHAR(10)&B1

    Works perfectly well... so long as you *DO* remember to format the cell to wrap text! (Duuuuhhhhhhhhhh!)

    Regards
    Peter

  8. #8
    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: Insert CR/LF In Text? (Excel 97-SR2)

    Just a comment:
    The "char(10)" trick is a good way to wrap text that you use for datalabels and text boxes when they are "linked" to cells. There is no other good way to wrap these linked cells.

    A variation of this "trick" can be used in custom formats to "wrap" DISPLAYS of dates (for example). In this case you must eplicitly type alt-0010 (from the numeric keypad) to add the LineFeed code.

    Steve

Posting Permissions

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