Excel - New Lines within a Cell

Martynt74 · 5 Jun 2019 at 13:51

I know when entering text you can hit "ALT+ENTER" to start a new line. However this doesn't seem to work when using formulas annoyingly.

I have 3 columns of data

Count
Revenue
Margin

These are organised into columns of ranking (AA, AB, AC, BA, BB, BC etc). I want to show them in a table like this

However i can't seem to get the formatting to work. As such as i start using a formula it concatenates everything onto a single line.

Anyone have any idea if what i want to do is possible?

Martynt74 · 5 Jun 2019 at 13:56

Google to the rescue, just a case of finding the right search description

Solution

Inserting Line Breaks Using Formulas

If you’re working with text data and want to insert line break in Excel formula results, you can do that as shown below.

In the formula, where you want to insert a line break, add Char(10).

For example, instead of =A1&A2, use =A1&Char(10)&A2.
CHAR formula returns the character based on the ASCII code. And in this case, 10 represents the ACSII code for a line break.

Select the cell with the formula, go to Home –> Alignment –> Wrap Text

If the Wrap Text is NOT applied, adding Char(10) would make no changes in the formula result.

Note: If you are using Mac, use Char(13) instead of Char(10).

chroniclard · 5 Jun 2019 at 13:56

Multiple formulas in one cell? Use CHAR(10) between them

="abc" & CHAR(10) & "def"

Need word wrap formatted on the cell.

Martynt74 · 5 Jun 2019 at 13:57

Beat you by seconds.

Although thank you