How do I insert a carriage return in a text formula?

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: How do I insert a carriage return in a text formula?

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I know I can do it manually with alt+enter, but how can I insert a carriage return in a formula? Using char(10) or char(13) just gives me the little black box.

    Thanks,

    Jon

    [ This Message was edited by: fearless613 on 2002-05-30 09:44 ]

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try the following:

    ActiveCell.FormulaR1C1 = "First Line" & Chr(10) & "Second Line" & Chr(10) & "Third Line"
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ="Jack and Jill"&CHAR(10)&"went up the hill..."

    Format the cell containing this formula with a "Wrap text" Alignment

    [ This Message was edited by: Mark W. on 2002-05-30 09:55 ]

  4. #4
    New Member
    Join Date
    May 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Your solutions work fine with text in quotes:
    = "First Line" & Chr(10) & "Second Line" & Chr(10) & "Third Line"

    But I'm working with cell references:
    = B10&CHAR(10)&C10&CHAR(10)&D10
    (which doesn't work...)

    Any ideas?


  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    John,

    you have a leading space after your "=" sign in your formula..... is this your problem ?

    If not, I guess your cells you're referencing can also contain numbers as well as text, these two approaches do seem to work :

    =TEXT(A1,"#,###.00")&CHAR(10)&TEXT(A2,"#,###.00")&CHAR(10)&TEXT(A3,"#,###.00")&CHAR(10)&TEXT(A4,"#,###.00")&CHAR(10)&TEXT(A5,"#,###.00")&CHAR(10)&TEXT(A6,"#,###.00")&CHAR(10)

    =A1&CHAR(10)&A2&CHAR(10)&A3&CHAR(10)&A4&CHAR(10)&A5&CHAR(10)&A6&CHAR(10)
    :: Pharma Z - Family drugstore ::

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Microsoft Excel - Book3___Running: xl2002 XP : OS = Windows (32-bit) NT 5.00
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
    A1=Jack

    A
    B
    C
    D
    1
    Jack:alert('=A1&CHAR(10)&A2&CHAR(10)&A3&CHAR(10)&A4&CHAR(10)&A5&CHAR(10)&A6&CHAR(10)')>Jack
    B
    in
    D
    the
    UK


    2
    B:alert('=A1&CHAR(10)
    &A2&CHAR(10)
    &A3&CHAR(10)
    &A4&CHAR(10)
    &A5&CHAR(10)
    &A6&CHAR(10)')>Jack
    B
    in
    D
    the
    UK
    Jacks Way (nice and simple)
    3
    in


    4
    D


    5
    the
    Wrap Text donít forget
    6
    UK


    7




    Sheet1

    To see the formula in the cells just click on the cells hyperlink

    The above image was automatically generated by [HtmlMaker V1.27]
    If you want FREE SOFT, click here and Colo will email the file to you
    This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo

    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Or Try ....

    Microsoft Excel - Book4___Running: xl2002 XP : OS = Windows (32-bit) NT 5.00
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
    A1=Jack

    A
    B
    C
    D
    E
    1
    Jack:alert('=TEXT(A1,%22#,###.00%22)&CHAR(10)&TEXT(A2,%22#,###.00%22)&CHAR(10)&TEXT(A3,%22#,###.00%22)&CHAR(10)&TEXT(A4,%22#,###.00%22)&CHAR(10)&TEXT(A5,%22#,###.00%22)&CHAR(10)&TEXT(A6,%22#,###.00%22)&CHAR(10)')>Jack
    In
    the
    uk
    Mr
    Excel



    2
    In :alert('=TEXT(A1,%22#,###.00%22)
    &CHAR(10)
    &TEXT(A2,%22#,###.00%22)
    &CHAR(10)
    &TEXT(A3,%22#,###.00%22)
    &CHAR(10)
    &TEXT(A4,%22#,###.00%22)&
    CHAR(10)&
    TEXT(A5,%22#,###.00%22)
    &CHAR(10)
    &TEXT(A6,%22#,###.00%22)
    &CHAR(10)')>Jack
    In
    the
    uk
    Mr
    Excel


    Jacks way
    3
    the



    4
    ukEasy readining your agree!


    5
    Mr



    6
    ExcelHope this helps a bit to read clearer!


    Sheet1

    To see the formula in the cells just click on the cells hyperlink

    The above image was automatically generated by [HtmlMaker V1.27]
    If you want FREE SOFT, click here and Colo will email the file to you
    This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo

    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Chris, hope you dont mind recyled your formulas, not edit one bit, so as your work, with my bent world, and a touch of Jack!

    Bent means when servers are bent IE dont work, fallen over, no other meaning intended, before i get ripped apart, servers bent -crashed!
    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    no worries mate !

    (WHAT is the "wi" in your signature ????)

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ††
    A trick gangster cures towards anyone unhelpful.

    Nar,, ive messed up the sigy, with my bad HTML

    Il sort it, as always mate i care to much and try to help to much, so the sigy to big, OK in text HTML goes ^!^!#^$

    But im rubbish at Excel so my HTML stinks.

    Take care mate hope your fit and well soon, chat in a bit bed now very late in London 1 or 2am by now i guess.

    My children wake at 6am,.... logoff, beer Cuban cigar and sleep, snore twice and upÖ new day in this house, love the boys watched England pay dreadful, I teach them Football I was good at, if a bit hard and aggressive, ho hum. Nothing else in my life even been any good at bar watching formula 1 that is

    Take care.

    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

DMCA.com