Did you know that there is a simple way to insert text on a new line inside a single cell? In this article, I will show you how to insert a line break in a Google Sheets cell and how to insert a line break in a formula. Then, I will show you several automatic line break formulas that will allow you to insert or remove line breaks automatically.
Keyboard Shortcut to Insert a New Line in Cells/Formulas
The keyboard shortcut to insert a new line in a cell or a formula is as follows (to be used while editing the cell):
- Ctrl + Enter (Windows)
- Command ⌘ + Return (Mac)
Pressing Ctrl + Enter / Command ⌘ + Return on the keyboard inserts the line break character (carriage return). Just like the “space” character, it is invisible, but it is still a “character” (like punctuation).
To edit a cell, you can double-click on it, or you can click once on the cell and then press Enter, or you can simply start typing after selecting a cell. When the cell is being edited, you can use the above shortcut to insert a new line in a cell. When you do this, you will see the cursor blinking from one line to another, indicating which line you are about to type on.
After new lines have already been inserted in the cell, you can use the up and down arrow keys to navigate between the lines.
Inserting New Lines Wherever You Want
To select the exact place where you want to insert a new line, hover the cursor in the formula bar and click where you want to insert a new line. You will see the cursor blinking at the place where the next character / new line will be inserted.
Automatically Wrapping Text
There is a feature in Google Sheets that allows text to automatically wrap to a new line when the text reaches the end of a cell, just like when you enter paragraphs in a Word document and the text wraps to the next line when the page ends. This feature is called “Wrap.”
After applying the “Wrap” format to the cell, when the text reaches the end of the cell (determined by the column width), the next text will automatically move to a new line.
Inserting a New Line in a Cell
Let’s start with an example of using the keyboard shortcut to insert new lines within a single cell. Let’s say we have a list of tasks to enter, but we want each task to be written on its own line inside a single cell. For this, we will use the keyboard shortcut to add new lines in the cell.
To insert a new line in a cell, enter 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), then enter the text that should appear on the second line, and repeat the process to add more lines inside the cell.
Notice in the above image, in cell A2, the task names appear each on a separate line inside a single cell. Additionally, 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 cells with multiple lines, you can use bullet points to make the list easier to read.
Or you can indent the cells / items inside the cells to further organize your list and make it easy to read.
Inserting a New Line in a Formula
You can also use the Ctrl + Enter / Command ⌘ + Return keyboard shortcut to add new lines in formulas. This is useful when you want to organize your formulas and make them easy to read. This is particularly helpful for very long formulas, but in this example, we will use a regular IF formula for demonstration purposes.
Take a look at how the formula below is written on two lines. Also, notice the formula below it, which is the same formula written on a single line. Both formulas do the same thing, and both can be copied and pasted into your sheet, and so, as you can see… writing a formula on multiple lines does not affect its functionality.
To insert a new line in a formula in Google Sheets, enter the part of the formula for the first line, then press Ctrl + Enter on the keyboard, then enter the next part of the formula, and repeat these steps if you want more than two lines in your formula. You can also enter the formula first, then edit the formula to insert a new line later… for this, simply click in the formula bar where you want to add a new line (a blinking cursor will appear), then press Ctrl + Enter to add a new line.
So, when the above formula was created, we first typed =IF( , then we pressed Ctrl + Enter on the keyboard, then we typed the remaining part of the formula. We could have also typed the formula on a single line, then clicked where we wanted the line break to occur, before pressing Ctrl + Enter on the keyboard.
(The above formula 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 a Single 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:
- Type the first formula
- Type an ampersand (&)
- Type char(10)
- Type an ampersand (&)
- Type the second formula
- Press “Enter” on the keyboard
The CHAR function gets/displays specific characters, and =CHAR(10) gets/displays the “new line” character. When we use the ampersand to combine the formulas with the CHAR(10) function as shown below, it allows you to use multiple formulas in a single cell, where the value generated by each formula is displayed on a separate line.
We are simply telling Google Sheets to insert 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 will perform two different calculations with these numbers, and we will display the results of both calculations/formulas in a single cell. For this, we will display the results of each formula on a different line, or in other words, we will insert a “new line” character between each formula.
=B2-A2&CHAR(10)&B2+A2
If you prefer, instead of using CHAR(10), you can simply type a new line character in quotes, using the same keyboard shortcut “Ctrl + Enter,” 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 the method you prefer. I personally prefer actually typing the new line character, as in the formula below.
=B2-A2&""&B2+A2
As you can see in the image above, the gain/loss is indicated on the upper line of the cell, and the total is indicated on the lower line of the cell.
Line Break Locations for Formulas
Note that Google Sheets will not preserve the line break if the line break is placed in certain locations, such as after commas in the above example formula. If you put a line break / new line after the comma in that formula, Google Sheets would revert to the original formula after pressing Enter on the keyboard.
However, after the opening parenthesis (after the start of a new function), that is a good place to put a line break (as shown in the above example). You can also put a line break after the equal sign. If you place the line break in the correct place, Google Sheets will retain the new lines in the formula after pressing Enter on the keyboard.
Automatic Line Break Formulas
Let’s now move on to formulas that will allow you to automatically add or remove line breaks. Here are several ways to do it.
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, as shown below:
=CHAR(10)
Or you can directly enter the new line character, in quotes, as shown below, as well as in the alternative formulas for each example. To enter a new line character in quotes, simply type a quote, then press Ctrl + Enter on the keyboard, then type another quote.
""
The above two methods are two different ways to specify the “new line” character in Google Sheets.
In the example images below, you will see that we use the CHAR function, but below each image, you will find the alternative version of the formula with the new line character entered in quotes instead of the CHAR function.
Formulas with Multiple Lines vs. Formulas with New Line Character Inserted in Quotes
Note that there is a difference between formulas that simply have multiple lines and formulas that have a new line character inserted in quotes. Generally, when a formula is written on multiple lines, it does not affect the functionality of the formula…
But when we put a new line character in quotes in a formula (as shown in the examples below without the CHAR function), that is when the new line character affects the functionality of the formula (as intended/desired).
Combine Multiple Cells into New Lines Inside a Single Cell
For our first type of formula, let’s go over how to combine the contents of multiple cells into new lines inside a single cell, automatically using a formula. For this, we will use the ampersand operator / & to combine the cells, where there is a new line character between each cell reference.
So, we will specify one cell, then specify that we want a new line, then we will specify the next cell, and we will repeat the process until all desired cells have been vertically combined into new lines inside a single cell. You can think of this process as “stacking the cells.”
Let’s say we have 1 task listed in each cell, and we want each task to be on its own new line inside a single cell. We will use the following formula for this.
The formula below is entered in cell B1 (Task names are entered in cells A1 and A2):
=A1&CHAR(10)&A2
Again, if you prefer, you can use the new line character directly (entered in quotes) instead of the CHAR function, as shown in the formula/example image below.
The formula below is entered in cell B1 (Task names are entered in cells A1 and A2):
=A1&""&A2
As you can see in both images above, the formula with the CHAR function does the same thing as the formula with the new line character entered in quotes. Both formulas do the same thing but are simply written in different ways.
For the rest of the example images, you will see that we use the CHAR function, but below each image is the alternative version of the formula with the new line character entered in quotes.
Replace a Character with a Line Break
If you have text entered in a cell, where you want to automatically insert line breaks where a certain character is found, you can do so using the SUBSTITUTE function. The SUBSTITUTE function replaces a specified text string with another specified text string. In this example, we will use the SUBSTITUTE function to replace a specified character (a punctuation mark) with a line break.
When using the SUBSTITUTE function, you first specify the cell/text to search in, then you specify the text to search for/replace, then you specify the replacement text.
The example image below shows two different scenarios. In the first scenario (top of the image), we have sentences entered, where each sentence ends with a period. We want each sentence to appear on its own new line inside the cell. For this, we will replace each period found in cell A2 with a line break.
In the second scenario (bottom of the image), we have task names entered, where each task is separated by a comma. We want each task name to appear on its own new line inside the cell. For this, we will replace each comma found in cell A5 with a line break.
Formulas:
The following formula replaces each period with a line break. The formula is entered in cell A2, and the sentences are entered in cell A1.
=SUBSTITUTE(A1,". ",CHAR(10))
The following formula replaces each comma with a line break. The formula is entered in cell A5, and the task names are entered in cell A4.
=SUBSTITUTE(A4,", ",CHAR(10))
Alternative formula for replacing the period with a line break:
=SUBSTITUTE(A1,". ","")
Alternative formula for replacing the comma with a line break:
=SUBSTITUTE(A4,", ","")
As you can see in the two images above, the SUBSTITUTE function replaces the period at the end of the sentence with line breaks, which puts each sentence on its own line inside a single cell.
The second formula replaces all the commas that separate the task names with line breaks… which puts each task name on its own line inside the cell.
Removing Line Breaks Horizontally Inside a Cell
You can also use the SUBSTITUTE function to remove line breaks when you have text on multiple lines in a cell and want to put them on a single line. This is the opposite of what we did in the previous example. For this, you simply reverse the criteria in the SUBSTITUTE function, where instead of replacing a specified text with a line break… we replace the line breaks with a specified text. (Remember the alternative method to specify a line break character… where you insert a line break with the keyboard shortcut, in quotes? Specifying a “space” character is very similar, you simply type a space in quotes)
The formula below is entered in cell A2 (Task names are entered in cell A1):
=SUBSTITUTE(A1,CHAR(10)," ")
Alternative formula:
=SUBSTITUTE(A1,""," ")
As you can see in the example image above, the SUBSTITUTE function replaces the line breaks / new line characters with spaces, which removes the line breaks and puts the task names on a single line. If you want, you can specify a different character to replace the line breaks other than a space, such as a comma or a colon.
Removing Line Breaks Vertically Across Multiple Cells
Now let’s move on to another way to remove line breaks, where instead of taking multiple lines inside a cell and putting them on a single line inside the cell… we will take multiple lines from a single cell and put each line in its own cell (stacked vertically).
(In the next example, I will show you how to do the same thing, but vertically)
To do this, we need to use the SPLIT function, to split the content of a single cell into multiple cells.
When using the SPLIT function, you first specify the cell/text to split, then you specify the “delimiter,” which is the text we are going to use to split. So, in this case, we will split the text from cell A1, where there is a line break / new line character.
The formula below is entered in cell A2 (Task names are entered in cell A1):
=SPLIT(A1,CHAR(10))
Alternative formula:
=SPLIT(A1,"")
This content was originally created by Corey Bustos / Crawlan.com.
Click here to get your Google Sheets cheat sheet.
Quiz: Test Your Knowledge
Answer the following questions about inserting a new line inside a cell to test your knowledge! Scroll down to find the quiz answers.
Question 1
Which keyboard shortcut among the following allows you to insert a new line in a cell?
- Shift + Enter
- Ctrl + B
- Ctrl + Enter
Question 2
Which formula represents the new line character among the following?
- =CHAR(1)
- =CHAR(10)
- =NEWLINE()
Question 3
True or False: Google Sheets can automatically insert line breaks using Wrap.
- True
- False
Answers to the questions above:
- Question 1: 3
- Question 2: 2
- Question 3: 1
Find all this information on Crawlan.com.
