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

Top image for the lesson on how to insert a new line in a cell in Google Sheets, and how to insert a new line in a formula (Plus how to create automatic line breaks including how to remove line breaks and how to replace a character with line breaks (Lesson by SpreadsheetClass.com)

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 cell in Google Sheets, follow these steps:

  • Type the text that you want to be on the first line within the cell
  • While the cell is still being edited, press Ctrl + Enter on the keyboard, and the cursor will go to a new line / a new line will be added within the same cell
  • Type the text that you want to be on the second line
  • Repeat the steps to insert more lines within the cell

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 on the keyboard, 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

Click here to get your free 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

Pressing Ctrl + Enter 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.

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

To wrap text in Google Sheets, follow these steps:

  • Select the cell(s) that you want to wrap
  • On the top toolbar, click the "Text wrapping" menu / button (looks like a curved arrow)
  • Click "Wrap" (Button also looks like a curved arrow)
  • Adjust the width of the column if needed

After applying "Wrap" 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.

Click here to learn more about how to wrap and unwrap text in Google Sheets.

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 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.

An example of how to insert a new line in cell in Google Sheets by using the Ctrl and Enter keyboard method (Keyboard shortcut and example all in one)

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.

Insert a new line in a formula

You can also use the Ctrl + Enter 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.

The formula below is entered into cell A2 for this example (The number 1 is entered into cell B2):

=IF(
B14=1,"Yes","No")

Same formula on a single line:

=IF(B14=1,"Yes","No")

An example of how to insert a new line in a formula in Google Sheets by using the Ctrl and Enter keyboard method (Keyboard shortcut and example in a single image)

So when the formula above was created, first we typed =IF( and then we press 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)

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.

To stack the contents of cells into multiple lines within a single cell, follow these steps:

  • Type a cell reference, like A1
  • Type an ampersand / & operator symbol
  • Type =CHAR (10)
  • Type the next cell reference
  • The final formula will look like this =A1&char (10)&A2 which will stack the contents of cells A1 and A2, vertically onto multiple lines within a single cell

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

=A1&char (10)&A2

An example of how to combine multiple cells into new lines in a single cell in Google Sheets first method by using the CHAR function with the ampersand

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

An example of how to combine multiple cells into new lines in a single cell in Google Sheets alternate method with the newline character with an ampersand

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))

An example of how to replace a character with a line break in Google Sheets by using the substitute function (Example using commas and example using periods all in one)

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)," ")

An example of how to remove line breaks horizontally within a cell in Google Sheets by using the substitute function

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))

An example of how to remove line breaks horizontally into multiple cells in Google Sheets by using the SPLIT function

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)))

An example of how to remove line breaks vertically into multiple cells in Google Sheets by using the TRANSPOSE function and the SPLIT function

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 free Google Sheets cheat sheet