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

Did you know that you can easily insert text on a new line within a single cell in Google Sheets? In this article, we will explore how to insert a new line in a cell and in a formula. Additionally, we will cover various automatic line break formulas to add or remove line breaks effortlessly.

How to Insert a New Line within a Formula in Google Sheets

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

• Type the portion of the formula that you want on the first line within the cell.
• While still editing the cell, press Ctrl + Enter on the keyboard. This will add a new line within the same cell.
• Type the next portion of the formula on the second line.
• Repeat the above steps to insert more lines within the formula.

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 Ctrl + Enter. When you press Ctrl + Enter, Google Sheets recognizes it as the “New Line” character. Although invisible, it functions as a character similar to punctuation marks. You can use this shortcut when the cell is being edited to insert a new line in a cell. The flashing cursor will move from one line to the next, indicating the line where you are about to type.

Entering New Lines Anywhere You Want

To specify exactly where you want to insert a new line, hover your cursor over the formula bar and click where you want to put the new line. The cursor will start flashing where the next character or new line will be inserted.

Make Text Go to a New Line Automatically

Google Sheets offers a feature called “Wrap Text” that automatically moves text to a new line when it reaches the end of a cell. This feature is useful for typing paragraphs, similar to popular word processing software. After applying the “Wrap” formatting to a cell, text will automatically go to a new line when it reaches the end of the cell determined by the column width.

Insert a New Line within a Cell

To illustrate how to insert a new line in a cell, let’s consider a task list where each task needs to be written on its own line within a single cell. Follow these steps:

1. Type the text that should appear on the first line.
2. Press Ctrl + Enter on the keyboard while still editing the cell.
3. Type the text that should appear on the second line.
4. Repeat the process to add more lines within the cell.

Insert a New Line in a Formula

You can also use the Ctrl + Enter keyboard shortcut to add new lines in formulas. This is particularly useful for organizing formulas, especially when dealing with large ones. Let’s take a look at an example using a simple IF formula:

=IF(
  condition,
  value_if_true,
  value_if_false
)

The formula above is written on two lines for better readability. However, it can also be written on a single line without affecting its functionality. To insert a new line in a formula, type the portion of the formula for the first line, press Ctrl + Enter on the keyboard, and then type the next portion of the formula. Repeat these steps if you want more than two lines in your formula. You can also enter the formula first and then edit it to insert a new line by clicking in the formula bar, where a blinking cursor will appear, before pressing Ctrl + Enter on the keyboard.

Using Multiple Formulas in One Cell

Instead of inserting a new line in the formula bar itself, you can insert a new line in a cell to use multiple formulas within a single cell. This allows the values generated by different formulas to be displayed on separate lines.

To use multiple formulas in a single cell:

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

The CHAR function retrieves specified characters, and CHAR(10) retrieves the “new line” character. By combining formulas with the CHAR(10) function using ampersands, you can display the results of each formula on a different line within the cell. For example, if you want to calculate the difference and sum of two numbers, you can display the difference on the first line and the sum on the second line in a single cell using the following formula:

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

Alternatively, you can use quotation marks to insert a new line character, like in the formula below:

=B2-A2&" "&B2+A2

As shown in the image above, the gain/loss appears on the top line of the cell, and the total appears on the bottom line.

Line Break Locations for Formulas

When inserting a line break in a formula, it’s crucial to place it correctly. Google Sheets won’t save the line break if it is put in certain places, such as after commas in a formula. However, it is suitable to put a line break after an opening parenthesis or after the equal sign. By placing the line break in the correct location, Google Sheets will retain the new lines in the formula even after pressing enter.

Automatic Line Break Formulas

Now let’s explore formulas that allow you to automatically add or remove line breaks. Here are a few examples:

Using the CHAR Function vs. Using the New Line Character

There are two ways to specify the “new line” character in Google Sheets. The first method is to use the CHAR function:

=CHAR(10)

Alternatively, you can directly input the new line character between quotation marks, like this:

""

Both methods represent the “new line” character. In the example images below, we use the CHAR function, but the formulas also have alternate versions using the new line character between quotation marks.

Combine Multiple Cells into New Lines within a Single Cell

Combining the contents of multiple cells into new lines within a single cell can be achieved by using the ampersand (&) operator to concatenate cells. Simply specify a cell, add a new line, and then specify the next cell. Repeat this process until all desired cells have been vertically combined into new lines within a single cell.

For example, if you have a task list where each task is listed in a separate cell, and you want to put each task on a line within a single cell, use the following formula:

=A1&CHAR(10)&A2

Alternatively, you can use the new line character between quotation marks:

=A1&""&A2

By using the CHAR function or the new line character, the formula will combine the task names from multiple cells into new lines within a single cell.

Replace a Character with a Line Break

To automatically insert line breaks where a specific character is found, you can use the SUBSTITUTE function. The SUBSTITUTE function replaces specified text with other specified text. In this case, we will replace a certain character, such as punctuation, with a line break.

For instance, suppose you have sentences in a cell, and you want each sentence to appear on its own line within the cell. You can use the SUBSTITUTE function to replace every period with a line break:

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

Alternatively, you can replace every comma with a line break by using the following formula:

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

As shown in the example image, the SUBSTITUTE function replaces the specified characters with line breaks, achieving the desired result.

Remove Line Breaks Horizontally within a Cell

If you have text with multiple lines in a cell and want to remove line breaks to put the text on a single line, you can use the SUBSTITUTE function. By switching the criteria, you can replace line breaks with specified text.

To remove line breaks and put task names on a single line, replace line breaks with a space character. The formula would look like this:

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

Alternatively, you can use the following formula:

=SUBSTITUTE(A1,""," ")

As shown in the example image, the SUBSTITUTE function replaces line breaks with spaces, putting all task names on one line.

Remove Line Breaks Horizontally into Multiple Cells

To remove line breaks and split multiple lines into individual cells horizontally, you can use the SPLIT function. The SPLIT function splits the contents of a single cell into multiple cells using a specified delimiter. In this case, we want to split the text in a cell by the new line character.

For example, let’s say you have task names entered in multiple lines within a single cell, and you want each task name to be split into its own cell. Use the following formula:

=SPLIT(A1,CHAR(10))

Alternatively, you can use the following formula:

=SPLIT(A1,"")

By using the SPLIT function, the task names are split horizontally into individual cells, as depicted in the example image.

Remove Line Breaks Vertically into Multiple Cells

To split task names (entered in multiple lines within a single cell) into individual cells vertically, you can use the TRANSPOSE function in combination with the SPLIT function. The TRANSPOSE function switches columns to rows and rows to columns.

Wrap the SPLIT function with the TRANSPOSE function to achieve this:

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

Alternatively, you can use the following formula:

=TRANSPOSE(SPLIT(A1,""))

With the help of the TRANSPOSE function, the task names are split vertically into separate cells, with each task name in its own cell.

For more tips and tricks on using Google Sheets, visit Mr Reviews.

Pop Quiz: Test Your Knowledge

Answer the following questions to refine your understanding of inserting a new line inside a cell.

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

   • Shift + Enter
   • Ctrl + B
   • Ctrl + Enter

2. Which of the following formulas represents the new line character?

   • =CHAR(1)
   • =CHAR(10)
   • =NEWLINE()

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

   • True
   • False

Answers:

• Question 1: Ctrl + Enter
• Question 2: =CHAR(10)
• Question 3: True