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

fearless613 · May 30, 2002

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

Al Chara · May 30, 2002

Try the following:

ActiveCell.FormulaR1C1 = "First Line" & Chr(10) & "Second Line" & Chr(10) & "Third Line"

Mark W. · May 30, 2002

="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

fearless613 · May 30, 2002

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?

Chris Davison · Jun 2, 2002

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)

Jack in the UK · Jun 2, 2002

<CENTER><TABLE ALIGN=CENTER BORDER=1 BORDERCOLOR=#C0C0C0 CELLSPACING=0><TR><TD COLSPAN=5 BGCOLOR=#0C266B ><TABLE ALIGN=CENTER BORDER=0 WIDTH=100%><TR><TD ALIGN=LEFT>Microsoft Excel - Book3</TD><TD ALIGN=RIGHT>___Running: xl2002 XP : OS = Windows (32-bit) NT 5.00</TD></TR></TABLE></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=5>(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp</TD></TR><TR><TD BGCOLOR=WHITE COLSPAN=5><TABLE BORDER=0><TR><TD COLSPAN=5% ALIGN=CENTER BGCOLOR=White>A1</TD><TD COLSPAN=10% ALIGN=RIGHT BGCOLOR=#D4D0C8 >=</TD><TD COLSPAN=85% ALIGN=LEFT BGCOLOR=White>Jack</TD></TR></TABLE></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER>
</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>A</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>B</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>C</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>D</CENTER></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>1</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >Jack</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript: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
</A></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>2</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >B</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript: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
</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >Jacks Way (nice and simple)</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>3</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >in</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>4</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >D</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>5</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >the</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#000000 ALIGN=Center VALIGN=BOTTOM >Wrap Text don’t forget</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>6</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >UK</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>7</CENTER></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD COLSPAN=5>Sheet1</TD></TR></TABLE>
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, <A HREF=mailto:corosuke@chan.co.jp>click here</A> and Colo will email the file to you
This code was graciously allowed to be modified: by <A HREF=mailto:ivanmoala@xtra.co.nz>Ivan F Moala</A> All credit to Colo
</CENTER>

Jack in the UK · Jun 2, 2002

Or Try ....

<CENTER><TABLE ALIGN=CENTER BORDER=1 BORDERCOLOR=#C0C0C0 CELLSPACING=0><TR><TD COLSPAN=6 BGCOLOR=#0C266B ><TABLE ALIGN=CENTER BORDER=0 WIDTH=100%><TR><TD ALIGN=LEFT>Microsoft Excel - Book4</TD><TD ALIGN=RIGHT>___Running: xl2002 XP : OS = Windows (32-bit) NT 5.00</TD></TR></TABLE></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=6>(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp</TD></TR><TR><TD BGCOLOR=WHITE COLSPAN=6><TABLE BORDER=0><TR><TD COLSPAN=5% ALIGN=CENTER BGCOLOR=White>A1</TD><TD COLSPAN=10% ALIGN=RIGHT BGCOLOR=#D4D0C8 >=</TD><TD COLSPAN=85% ALIGN=LEFT BGCOLOR=White>Jack</TD></TR></TABLE></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER>
</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>A</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>B</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>C</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>D</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>E</CENTER></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>1</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >Jack</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript: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
</A></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>2</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >In </TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript: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
</A></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#000000 ALIGN=Left VALIGN=BOTTOM >Jacks way</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>3</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >the</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>4</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >uk</TD><TD BGCOLOR=#000000 ALIGN=Left VALIGN=BOTTOM >Easy readining your agree!</TD><TD BGCOLOR=#000000>
</TD><TD BGCOLOR=#000000>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>5</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >Mr</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>6</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >Excel</TD><TD BGCOLOR=#000000 ALIGN=Left VALIGN=BOTTOM >Hope this helps a bit to read clearer!</TD><TD BGCOLOR=#000000>
</TD><TD BGCOLOR=#000000>
</TD><TD BGCOLOR=#000000>
</TD></TR><TR><TD COLSPAN=6>Sheet1</TD></TR></TABLE>
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, <A HREF=mailto:corosuke@chan.co.jp>click here</A> and Colo will email the file to you
This code was graciously allowed to be modified: by <A HREF=mailto:ivanmoala@xtra.co.nz>Ivan F Moala</A> All credit to Colo
</CENTER>

Jack in the UK · Jun 2, 2002

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!

Chris Davison · Jun 2, 2002

no worries mate !

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

Jack in the UK · Jun 2, 2002

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.

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

fearless613

New Member

Excel Facts

Al Chara

MrExcel MVP

Mark W.

MrExcel MVP

fearless613

New Member

Chris Davison

MrExcel MVP

Jack in the UK

Well-known Member

Jack in the UK

Well-known Member

Jack in the UK

Well-known Member

Chris Davison

MrExcel MVP

Jack in the UK

Well-known Member

Similar threads

Forum statistics

Share this page

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

New Member

Excel Facts

MrExcel MVP

MrExcel MVP

New Member

MrExcel MVP

Well-known Member

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock