Page 1 of 2 12 LastLast
Results 1 to 10 of 14

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

This is a discussion on How do I insert a carriage return in a text formula? within the Excel Questions forums, part of the Question Forums category; I know I can do it manually with alt+enter, but how can I insert a carriage return in a formula? ...

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    2

    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

    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

    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

    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

    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,129

    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,129

    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,129

    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

    Default

    no worries mate !

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

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,129

    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"

Page 1 of 2 12 LastLast

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