What Is Line Breaks In Google Sheets?
The Line Breaks in Google Sheets inserts a new line in a cell, by breaking the text, string or a sentence into multiple parts and displays it in separate lines within a cell. In Google Sheets, to go to the next line in a cell, when we press enter like we commonly do to go to the next line, the cursor moves to the next cell instead of next line in the same cell. For this reason, we use the Google Sheets Line Breaks.
For example, we have a value given below and we will use the Line Break to break the value into three words. We have copied the value in the result cell to apply the Line Break feature.
Double-click cell B2 to edit, take the cursor after the word Wall and press “Alt+Enter”. Take the cursor after the word Street andpress “Alt+Enter”.
We get the output shown above. The Line Breaks have pushed the remaining letters to the next line within the same cell.
Key Takeaways
- The Line Breaks in Google Sheets is a feature that breaks the text string into multiple lines within a cell so that the entered text or any data is visible.
- We cannot remove Line Breaks on cells where the formula is applied, because when we double-click to manually delete using the Delete or the backspace keys, we will see the formula in edit-mode and not the values.
- If we have used the Text Wrapping method to insert Line Breaks, then use the “Overflow” or the “Clip” option to Unwrap it or to remove the Line Break as learnt in FAQ2. That is the only possible method.
- We can use the “Paint format” or the “Format Painter” method to wrap text too.
How To Insert Line Break In Google Sheets?
We can Insert Line Break in Google Sheets using the following ways, namely,
- With Keyboard Shortcut.
- With CHAR(10) Function.
- Wrap Text Option.
Method #1 – With Keyboard Shortcut
The steps to Insert Line Break using the Keyboard Shortcut are,
Step 1: Double-click on the cell value to get into the edit mode.
Step 2: Place the cursor where we must insert the Line Break and press the shortcut keys,
- For Windows, “Alt+Enter” or “Ctrl+Enter”.
- For MAC, “Alt+Return” or “Ctrl+Return” or “Command+Return”.
Method #2 – With CHAR(10)Function
[Special Note: In the UNICODE table, CHAR(10) is the code to insert a Line Break.]
The steps to Insert Line Break using the CHAR(10) Function are,
Step 1: Type =cell value in double-quotes or cell reference.
Step 2: Insert &CHAR(10). This is in continuation with the formula, where we want the Line Break. Proceed further.
Step 3: Type &cell value in double-quotes or cell reference.
Step 4: Insert &CHAR(10). It is in continuation with the formula, again, if we want the Line Break.
Step 5: Type &cell value in double-quotes or cell reference, and so on.
For example, if the cell values are in cells A1, A2 and A3 and the result cell A4 must have these values with Line Breaks, then the syntax will be =A1&CHAR(10)&A2&CHAR(10)&A3, as shown below.
We see that the result cells height is more than the other cells, indicating that the Line Breaks are applied.
Method #3 – Wrap Text Option
The steps to Insert Line Break using the Wrap Text Option are,
Step 1: Select the cell to wrap up the cell values.
Step 2: Go to the “Format” tab- click the “Wrapping” option right-arrow – select the “Wrap” option, as shown below.
Examples
Let us consider some Line Break In Google Sheets examples using the methods learnt in the previous section along with the Ampersand (&) and the CONCATENATE() functions.
Example #1
The dataset given below consists of the first names, last names of actors in a famous sitcom known as FRIENDS. The names are combined to display the full names in column C. However, we notice that the names are not visible. Let us use the Wrap text option to apply the Line Breaks.
The procedure to apply Line Break using Text Wrapping option is,
Select column C – go to the “Format” tab – click the “Wrapping” option right-arrow – select the “Wrap” option, as shown below.
We get the output shown below, with the full names visible and the Line Breaks applied.
Example #2 – Line Break with formula (& function)
The dataset consists of four people’s data, such as their names, occupation and their location. Let us insert Line Break using the Ampersand (&) function and the CHAR(10) methods.
The steps to insert Line Break using the Ampersand (&) function and the CHAR(10) are as follows:
Step 1: Select cell D2 and enter the formula =A2&CHAR(10)&B2&CHAR(10)&C2&CHAR(10), as shown below.
Step 2: Press “Enter”. Google Sheets gives an autofill option. select that or use the fill handle method.
We get the following output.
Example #3 – Line Break with formula (Concatenate)
Consider the Example #2 dataset again. Let us insert Line Break using the CONCATENATE function and the CHAR(10) methods.
The steps to insert Line Break using the CONCATENATE function and the CHAR(10) are as follows:
Step 1: Select cell D2 and enter the formula =CONCATENATE(A2&Char(10),B2&Char(10),C2&Char(10)), as shown below.
Step 2: Press “Enter” and drag the formula from cell D2 to D4 using the fill handle, to get the following results.
Example #4 – Replace Characters with line breaks in google sheets
The dataset given below consists of cartoon shows with hyphens “-” in-between, which is not required. We will Replace Characters with Line Breaks In Google Sheets using the SUBSTITUTE() and the CHAR(10) functions.
The steps to replace characters with Line Break using the SUBSTITUTE function and the CHAR(10) are as follows:
Step 1: Select cell B2 and enter the formula =SUBSTITUTE(A2,”-“,CHAR(10)), as shown below.
Step 2: Press “Enter” and drag the formula from cell B2 to B6 using the fill handle, as shown below,
How To Remove Line Breaks In Google Sheets?
We can Remove Line Breaks in Google Sheets using the following ways, namely,
- Using Delete key.
- Using backspace key.
- Using CLEAN() function.
Method #1 – Using Delete Key-
The steps to remove Line Breaks using Delete key are,
Step 1: Double-click the cell with applied Line Breaks.
Step 2: Place the cursor near the Line Break and press the “Delete” key.
Method #2 – Usig backspace key –
The steps to remove Line Breaks using backspace key are,
Step 1: Double-click the cell with applied Line Breaks.
Step 2: Place the cursor near the Line Break and press the “backspace” key.
Method #3 – Using CLEAN() function-
[Note: The CLEAN() function removes Line Breaks.]
The steps to remove Line Breaks using CLEAN() function are,
Step 1: In an empty cell, type =CLEAN( to get the following syntax. Go to the next step.
Step 2: Select the cell where the Line Breaks is applied.
Step 3: Close the brackets. Press “Enter”, to get the output without Line Breaks.
Important Things To Note
- We can break a text string into lines using the shortcut keys or the formula.
- The Line Breaks in Google Sheets uses ‘&,’ ‘CHAR,’ and ‘CONCATENATE’ functions.
- CHAR function returns the result based on a character code.
- The shortcut keys to use break lines feature is ‘Alt + Enter’ but this method works only one cell at a time. We must repeat the process to obtain results in other cells.
- The Line breaks feature in Google Sheets enable the Wrap Text option automatically.
Frequently Asked Questions (FAQs)
An alternate way to Insert Line Break using the Wrap Text option is,
First, select the cell to insert Line Break or Wrap Text.
Click the “Text wrapping” option on the Quick Access toolbar or the Toolbar, just below the Main Menu, as shown below.
Select the “Wrap” option, as shown below.
An alternate way to Remove Line Break using the Wrap Text option is,
First, select the cell where we have applied the Line Breaks using the Wrap Text method. Remember this will work only if we have applied the text wrap method for Line Break.
Click the “Text wrapping” option on the Quick Access toolbar or the Toolbar, just below the Main Menu, as shown below.
Select the “Overflow” or the “Clip” option, as shown in the below images.
OR
[Note: Alternatively, we already know the path to Wrap or Unwrap Text using the “Format à Wrapping à Overflow / Clip” path, as shown below.
A few reasons the Line Breaks in Google Sheets may not work are,
a. In the formula method, the data or cell reference is incorrectly entered.
b. The column-width or the row-height is already more to fit in the data without text wrap.
c. The applied Line Break on the cells are removed or modified.
Download Template
This article must help understand the Line Breaks in Google Sheets with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is Line Breaks In Google Sheets. We learn how to insert Line Break In Google Sheets & remove them, examples, working template. You can learn more from the following articles.
Leave a Reply