Insert a new line in cells / formulas in Google Sheets (+ Automatic line break formulas)

Did you know that there is a simple way to insert text on a new line, within a single cell? In this article I will show you how to insert a new line in a cell in Google Sheets, and I will also show you how to insert a new line in a formula. Then, I will show you a variety of automatic line break formulas that will allow you to insert line breaks or remove line breaks automatically.

To insert a new line within a formula in Google Sheets, follow these steps:

• Type the portion of the formula that you want to be on the first line within the cell
• While the cell is still being edited, press Ctrl + Enter for a Windows computer, or Command ⌘ + Return for Mac, and the cursor will go to a new line / a new line will be added within the same cell
• Type the next portion of the formula that you want to be on the second line
• Repeat the steps to insert more lines within the formula

Learn about the Google Sheets cheat sheet

Keyboard shortcut to insert new line in cells / formulas

The keyboard shortcut to insert a new line in a cell or a formula, is this (Use while editing the cell):

Ctrl + Enter (Windows)

Command ⌘ + Return (Mac)

• Command ⌘ + Return

Pressing Ctrl + Enter / Command ⌘ + Return on the keyboard inputs the New Line (carriage return) character. Much like the "Space" character it is invisible, but it is a "character" all the same (like punctuation).

To edit a cell, you can double click on it, or you can click on the cell once and then press enter, or you can simply start typing after selecting a cell. When the cell is being edited / is in edit mode, you can use the shortcut above to insert a new line in a cell. When you do this, you will see the flashing cursor go from one line to the next, indicating which line you are about to type on.

After new lines are already inserted within the cell, you can use the up and down arrow keys to navigate from line to line.

Entering new lines anywhere you want

To select exactly where you want to insert a new line, hover your cursor in the formula bar and click in the location where you want to insert a new line. You will see the cursor flashing where the next character / new line will be inserted.

Make text go to new line automatically

There is a feature in Google Sheets that will make text go to a new line automatically when the text reaches the end of a cell, like when you type paragraphs in a word document and when the text reaches the end of the [age it goes to a new line automatically. This feature is called "Wrap Text".

After applying the "Wrap text" formatting to the cell, when text reaches the end of the cell (determined by the width of the column), the following text will go to a new line automatically.

Insert a new line within a cell

First let's go over an example of using the keyboard shortcut to insert new lines within a single cell. Let's say that we have a task list to enter, but we want each task to be written on its own line within a single cell. To do this we will use the keyboard shortcut to add new lines in the cell.

To insert a new line in a cell, type the text that should appear on the first line, then press Ctrl + Enter (Command ⌘ + Return for Mac) on the keyboard (while the cell is still being edited), and then type the text that should appear on the second line, and repeat the process to add more lines within the cell.

Notice in the image above, in cell A2, that the task names each appear on an individual line within a single cell. Also, since cell A2 is selected, you can see how the text also appears on new lines / multiple lines in the formula bar.

When listing items in a cells with multiple lines, you may want to use bullet points to make the list easier to read.

Or you can indent cells / items within cells to further organize your list and make it easy to read.

Insert a new line in a formula

You can also use the Ctrl + Enter / Command ⌘ + Return keyboard shortcut to add new lines in formulas. This comes in handy when you want to keep your formulas organized and easy to read. This especially helps for very large formulas, but in this example we will use an ordinary IF formula for demonstration.

See how the formula below is written on two lines? Notice the formula below it, which is the same formula written on a single line. Both of these formulas do the same thing, both can be copied and pasted into your sheet, and so as you can see… writing a formula on multiple lines does not affect the way the formula works.

To insert a new line in a formula in Google Sheets, type the portion of the formula for the first line, then press Ctrl + Enter on the keyboard, and then type the next portion of the formula, and repeat these steps if you want more than two lines in your formula. You can also enter the formula first, and then edit the formula to insert a new line afterwards… to do this simply click in the formula bar where you want to add a new line (blinking cursor will appear), then press Ctrl + Enter to add a new line.

So when the formula above was created, first we typed =IF( and then we pressed Ctrl + Enter on the keyboard, and then we typed the rest of the formula. We could have also typed the formula on one line, and then clicked where we wanted the line break, before pressing Ctrl + Enter on the keyboard.

(The formula above will display the word "Yes" if cell B1 is equal to 1, and it will display the word "No" if cell B1 is not equal to 1)

Using multiple formulas in one cell

If you want, instead of inserting a new line in the formula bar itself, you can insert a new line in a cell so that you can use multiple formulas in a single cell, where the values generated by two formulas will be displayed on separate lines.

To insert a new line in a cell when using a formula, we use the ampersand / "&" operator, as shown below.

To use multiple formulas in a single cell in Google Sheets, follow these steps:

1. Type the first formula
2. Type an ampersand (&)
3. Type char(10)
4. Type an ampersand (&)
5. Type the second formula
6. Press "Enter" on the keyboard

The CHAR function gets / displays specified characters, and =CHAR(10) gets / displays the "new line" character. When we use the ampersand to combine formulas with the CHAR(10) function as shown below, this allows you to use multiple formulas in one cell, where the value generated by each formula is displayed on a separate line.

We are simply telling Google Sheets to put a new line between the two different formulas.

In the example image below, it shows that $100 was earned yesterday, and $120 was earned today. We are going to make two different calculations with these numbers, and we are going to display the results of both calculations / formulas in a single cell. To do this we will display the results of each formula on a different line, or in other words we will put a "new line" character between each formula.

We are going to display the difference between the two numbers on the first line in the cell (the gain / loss), and then we are going to display the sum of the two numbers on the second line in the cell (the total). So the first formula is B2-A2 and the second formula is B2+A2. The formula below puts a new line character between the formulas so that the result of the first formula appears on the first line, and the result of the second formula appears on the second line in the cell.

=B2-A2&CHAR(10)&B2+A2

If you want, instead of using CHAR(10) you can simply type a new line character between quotation marks, by using the same "Ctrl + Enter" shortcut, as shown in the formula below. When you do this, a new line will be inserted in the formula bar, and a new line will also be inserted in the cell. You can choose whichever method you like best. I prefer actually typing the new line character, like in the formula below.

=B2-A2&"
"&B2+A2

As you can see in the image above, the gain / loss is shown on the top line of the cell, and the total is shown on the bottom line of the cell.

Line break locations for formulas

Note that Google Sheets will not save the new line if there line break is put in certain places, such as after the commas in the example formula above. If you put a new line / line break after the comma in this formula, Google Sheets would revert back to the original formula after pressing enter on the keyboard.

But after the opening parenthesis (after starting a new function) makes a great place to put a line break (as shown in the example above). You can also put a line break after the equal sign. If you place the new line in the correct location, Google Sheets will keep the new lines in the formula after pressing enter on the keyboard.

Automatic line break formulas

Now let's go over the formulas that will allow you to automatically add or remove line breaks. Below are a variety of ways to do this.

Using the CHAR function vs. using the New Line character

There are two different ways to specify a "new line" character in Google Sheets. The first is to use the CHAR function, like below:

=CHAR (10)

Or, you can input the new line character itself, between quotation marks, as is shown below as well as in the alternate formulas for each example. To put a new line character between quotation marks, simply type a quotation mark, then press Ctrl + Enter on the keyboard, and then type another quotation mark.

"
"

So the two methods above are two different ways to specify the "new line" character in Google Sheets.

In the image for each example below you'll see that we are using the CHAR function, but below each image are the alternate formulas that use the new line character between quotation marks rather than the CHAR character.

Formulas with multiple lines vs. a formula with a New Line character inserted between quotation marks

Note that there is a difference between formulas that simply have multiple lines, and formulas that have a New Line character inserted between quotation marks. Usually when a formula is written on multiple lines it does not affect the way the formula works…

But when we put a New Line character between quotation marks in a formula (as shown in the examples below without the CHAR function), this is when the New Line character does affect how the formula works (as expected / desired).

Combine multiple cells into new lines within a single cell

For our first formula type, let's go over how to combine the contents of multiple cells, into multiple lines within a single cell, automatically by using a formula. To do this we will use the ampersand / & operator to combine cells, where there is a new line character between each cell reference.

So we are going to specify a cell, then specify that we want a new line, and then we will specify the next cell, and repeat the process until all of the desired cells have been vertically combined into new lines within a single cell. You can think of this process as "stacking cells".

So let's say that we have 1 task listed in each cell, and we want to put each task on a line within a single cell. We will use the formula below to do this.

The formula below is entered into cell B1 (The task names are entered into cells A1 and A2):

=A1&char (10)&A2

Again, if you want you can use the new line character itself (entered between quotation marks) in place of the CHAR function, as shown in the formula / example image below.

The formula below is entered into cell B1 (The task names are entered into cells A1 and A2):

=A1&"
"&A2

As you can see in the two images above, the formula with the CHAR function does the same thing as the formula with the new line character entered between quotation marks. The two formulas do the same thing, but are simply written in different ways.

For the rest of the example images you'll see that we are using the CHAR function, but beneath each image is the alternate version of the formula with the new line character inserted between quotation marks.

Replace a character with a line break

If you have text that is entered into a cell, where you want to automatically insert line breaks where a certain character is found, this can be done by using the SUBSTITUTE function. The SUBSTITUTE function replaces a specified string of text, with another specified string of text. In this example we are going to use the SUBSTITUTE function to replace a specified character (punctuation) with a line break.

When using the SUBSTITUTE function, first you specify the cell / text to search through, then you specify the text to search for / to be replaced, and then you specify the text to replace with.

The example image below shows two different scenarios. In the first scenario (on the top of the image), we have sentences typed out, where each sentence ends in a period. We want to make each sentence appear on its own new line within the cell. To do this we will replace every period that is found in cell A2, with a line break.

In the second scenario (on the bottom of the image), we have task names typed out, where each task is separated by a comma. We want to make each task name appear on its own new line within the cell. To do this we will replace every comma that is found in cell A5, with a line break.

The formula(s):

The formula below will replace each period with a line break. The formula is entered into cell A2, and the sentences are entered into cell A1.

=SUBSTITUTE(A1,". ",CHAR (10))

The formula below will replace each comma with a line break. The formula is entered into cell A5, and the task names are entered into cell A4.

=SUBSTITUTE(A4,", ",CHAR (10))

Alternate formula for replacing period with a line break:

=SUBSTITUTE(A1,". ","
")

Alternate formula for replacing comma with a line break:

=SUBSTITUTE(A4,", ","
")

Notice how in the example image above, the first formula has replaced all of the periods in the sentences with line breaks, which puts each sentence on its own line within a single cell.

The second formula has replaced all of the commas that separate the task names, with line breaks… which puts each task name on its own line within a single cell.

Remove line breaks horizontally within a cell

You can also use the SUBSTITUTE function to remove line breaks, where you have text on multiple lines in a cell and you want to put them on a single line. This is the opposite of what we did in the last example. To do this, all we need to do is switch the criteria in the SUBSTITUTE function, where instead of replacing specified text with a line break… we are replacing line breaks with specified text.

In this case, we have task names entered into a cell, where each task name is on its own line. We are going to remove the line breaks, so that the task names are typed out on a single line. To do this, we will replace the line breaks with a space / space character. (Remember the alternate method for specifying a line break character… where you insert a line break with the keyboard shortcut, between quotation marks? Specifying a "space" character is very similar, you simply type a space between quotation marks)

The formula below is entered into cell A2 (The task names are entered into cell A1):

=SUBSTITUTE(A1,char (10)," ")

Alternate formula:

=SUBSTITUTE(A1,"
"," ")

As you can see in the example image, the SUBSTITUTE function is replacing the line break / new line characters, with spaces / space characters, which removes the line breaks, and puts the task names all on one line. If you want, you could specify another character to replace the line breaks with other than a space, such as a comma, or a colon.

Remove line breaks horizontally into multiple cells

Now let's go over another way to remove line breaks, where instead of taking multiple lines within a cell and putting it onto a single line within the cell… we will take multiple lines from a single cell and put each line into its own cell (horizontally).

(In the next example I'll show you how to do the same thing, vertically)

To do this, we are going to need to use the SPLIT function, to split the contents of a single cell into multiple cells.

When using the SPLIT function, first we indicate the cell / text to split, then we indicate the "delimiter" which is the text that we are going to split by. So in this case, we are going to split the text in cell A1, where there is a line break / by the new line character.

The formula below is entered into cell A2 (The task names are entered into cell A1):

=SPLIT(A1,CHAR (10))

Alternate formula:

=SPLIT(A1,"
")

This content was originally created by Corey Bustos / SpreadsheetClass.com

Normally when specifying the delimiter (text to split by) for the SPLIT function, we would indicate a string of text like this: "Text"

But in this case we used the CHAR function to specify to split by a new line character. As you can see in the example image, the task names that were in multiple lines within a single cell, are now split horizontally into individual cells.

Remove line breaks vertically into multiple cells

Now let's do the same thing as in the previous example, except for this time we are going to split the task names which are entered into multiple lines within a single cell, into their own cells (vertically stacked). To do this we will use the TRANSPOSE function to transpose the results of the SPLIT function. The TRANSPOSE function switches columns to rows, and switches rows to columns.

Take the same formula from the last example, and wrap it with the TRANSPOSE function / nest it inside of the TRANSPOSE function, as shown in the formula / example image below.

The formula below is entered into cell B1 (The task names are entered into cell A1):

=TRANSPOSE(SPLIT(A1,CHAR (10)))

Alternate formula:

=TRANSPOSE(SPLIT(A1,"
"))

As you can see in the example image above, the task names (which were entered into multiple lines within a single cell) are being split apart and put vertically into individual cells, where each task name is in its own cell (vertically stacked).

Click here to get your Google Sheets cheat sheet

Pop Quiz: Test your knowledge

Answer the questions below about inserting a new line inside the cell, to refine your knowledge! Scroll to the very bottom to find the answers to the quiz.

Question #1

Which of the following keyboard shortcuts will insert a new line in a cell?

1. Shift + Enter
2. Ctrl + B
3. Ctrl + Enter

Question #2

Which of the following formulas represents the new line character?

1. =CHAR(1)
2. =CHAR(10)
3. =NEWLINE()

Question #3

True or False: Google Sheets can automatically insert line breaks by using text wrapping

1. True
2. False

Answers to the questions above:

Question 1:  3

Question 2:  2

Question 3:  1