Insert a Newline in Excel Formula (MacOS)

Question

I am using MS Excel on a Mac.

I have a formula, and I wish to insert a newline into it:

Guests!A1 & " " & Guests!B1 & [newline here] Guests!C1

How can I do this?

I was about to remove the tag, then I realised it is for "MacOS" not "macros". — Charles Roper, Jan 22 '10 at 20:10

richardtallent · Answer 1 · 2014-02-23T06:18:56.317

15

CHAR() is the appropriate function for an Excel formula, and you need character 10 (Line Feed) to create a line break. Example:

Guests!A1 & " " & Guests!B1 & CHAR(10) & Guests!C1

You'll need to have word-wrapping enabled on the cell, otherwise CHAR(10) will just look like a little square.

vbCRLF would be the right choice when using VBA (i.e., a macro). The question is about formulas, not VBA.

edited Feb 23 '14 at 06:18

answered Nov 04 '09 at 06:46

richardtallent

1

Mac OS Excel is still stuck in the days of when Classic Macs used CR for newline. You'll find that `CHAR(13)` is the one you want, and it's not portable between Mac & PC. You may be able to build an OS-sniffing function with IF and INFO("OSVERSION"). – scruss Jun 16 '14 at 21:22

score 3 · Answer 2 · answered Nov 09 '12 at 15:55

3

Press Command + Option + Enter

answered Nov 09 '12 at 15:55

Nguyen Minh Binh

with the cursor in the formula bar, not directly in the text of the cell – Rian Rizvi Jul 06 '19 at 22:26

score 0 · Answer 3 · answered Nov 04 '09 at 06:55

0

=Guests!A1 & " " & Guests!B1 & "<press Alt+Enter>  
" & Guests!C1

Replace <press Alt+Enter> by the actual key combination.
On a Mac, as Coxymla suggested, you will maybe replace Alt with another key ?

answered Nov 04 '09 at 06:55

iDevlop

1

That trick only works for straight text. If you have a formula it does add a separate line in the formula bar, but that will not translate to the cell formatting. richardtallent's suggesting of char(10) is the way to go. – guitarthrower Nov 04 '09 at 16:42

3 Answers3