When inputting or typing long sentences into your worksheet, you tend to lose sight of what you are typing because the sentence can keep going on and on. This also applies when you are importing texts and sentences from Google Docs or anywhere else.
This is where line breaks become a necessity. Line breaks aid concise reading, clarity and visibility. There are four known methods used to insert line breaks in google sheets.
In this tutorial, we are going to discuss these methods and How to Insert a Line Break in Google Sheets with every step screenshot. So, Let’s go
How To Add a New Line or Line Break in a Cell in Google Sheets.
As said earlier, there are four techniques used to insert new lines in your text. These techniques include the use of functions, formulas, add-ons or extensions and manual methods.
How to Add a Line Break in a formula in Google Sheets.
Method 1: Using the CHAR and UNICHAR functions.
These functions are similar in the way they operate. The CHAR function modifies a number into a character according to the Unicode table. It does a lookup in the Unicode table and finds a match for the selected number.
The difficult part is learning the different number codes and the character they represent. The CHAR function takes the format below.
=CHAR(table_number).
For a line break, its table number is ten (10). So its formula is typed in like so.
=CHAR(10)
In the worksheet below, we have a group of sentences. It is quite lengthy and cannot be seen completely. This calls for the CHAR function to insert line breaks.
Step 1: So, when we want to insert a line break, in a group of sentences, we can separate them into different columns.
We have divided the paragraph into four separate cells. The cells A2 to A5.
Step 2: In cell B2 in column B, we input a combination formula that contains the CHAR function and ampersand symbol (&).
=cell reference1 & CHAR(10) & cell reference2 & CHAR(10) & cell reference3 & CHAR(10) & cell reference4 & CHAR(10)
We substitute the cell references as cells A2 to A5. Therefore;
=A2&CHAR(10) &A3& CHAR(10) & A4& CHAR(10) & A5 & CHAR(10)
Step 3: The separated sentences are formed together with line breaks inserted in between.
UNICHAR Function.
The UNICHAR function is related to the CHAR function. However, in place of the table number in the CHAR formula, it requires a Unicode number. The Unicode number is a digit that represents the character to generate.
=UNICHAR(Unicode number)
Fortunately, the table number of a line break is the same as its Unicode number.
That is,
=UNICHAR(10)
Insert A Line Break using Keyboard Shortcut In Google Sheets
Using the Google Sheets desktop on your computer, laptop or MacBook, you can also insert line breaks using the shortcuts on your keyboard. The shortcut differs depending on the kind of device you use.
- Windows- Ctrl key + Enter or Alt key + Enter. These keys should be pressed simultaneously.
- MacBook- Ctrl key + Return or Alt key + Return. The Command key, ⌘ + Return can also insert new lines in your text but it only allows one line inserted at a time. The other methods allow the insertion of line breaks multiple times in the cell.
How to Add a New Line in Google Sheets Mobile App on iPhone, iPad and Android devices.
When inputting data on a worksheet using the Google sheet mobile app, inserting a line break can be tricky and difficult because there are no alt or ctrl keys on the Android or iPhone keypad.
So we are going to improvise and create a way to insert a line break in a cell. There are two methods employed.
- Using the CHAR and ampersand combination formula.
- Using the single letter to put in a line break.
Method 1: CHAR and Ampersand combination formula.
Step 1: Input the formula including the sentences or phrases into the result cell.
=” line A” & CHAR(10) & “line B” & CHAR(10) & “line C”
Substitute the sentences into the line A, B, and C in the formula.
Step 2: Click Enter and the sentences are separated by newlines or line breaks.
Method 2: Using a Single Letter.
Alternatively, in a string of sentences, you can insert a line break by typing a single letter in between the words or sentences.
Select the single letter. Press the Enter arrow on the keypad.
A line break is inserted, although there is a space in front of the first word in the next line. Be sure to erase any extra space in front of the word.
There is a drawback to this method, you can’t click enter on the keypad to proceed to the next cell below. If done, the line breaks inserted in the entire text are erased from the cell. If you want to move to another cell, by selecting the cell manually.
Inserting a New Line with Apps Script In Google Sheets
As stated in the second topic, line breaks can also be inserted by using add-ons and extensions. A notable add-on used is the Apps Script.
The Apps Script is a cloud-based javascript that is used to build simple codes, extract addresses and maps, generate custom names, dialogues, sidebars and create functions and formulas. Here, we are going to examine how the Apps Script is used to insert a line break.
The code pasted below is required for this method. Remember to copy the code below before using this add-on.
function onEdit(e) { if (SpreadsheetApp.getActiveSpreadsheet().getSheetName() == ‘Apps Scripts’) { if (typeof e.value != ‘object’) { e.range.setValue(e.value.replace(“, “, “\n”)); } } }
Step 1: Click on Extensions on the toolbar.
Step 2: Click on Apps Script.
Step 3: You would be redirected to the Apps Script webpage.
Step 4: Click on the Code g.s on the left side of the webpage.
Step 5: A dummy function is stated here, delete it and paste the copied code there.
Step 6: You can choose to rename the code. I renamed mine as Insert Line breaks.
Step 7: Click on the Save icon. The code is saved and becomes a component of the worksheet.
Note that the saved code won’t take effect until the sheet is renamed as Apps Script.
Step 8: The code replaces any repetitions of space and comma with an extra line. Any new text inputted in the worksheet automatically gets a line break inserted when required.
Replacing Characters with Line Breaks in Google Sheets.
You can also replace specific characters such as commas and spaces with a line break by using the SUBSTITUTE and REGEXREPLACE functions.
- =SUBSTITUTE(text_to_search, search_for, replace with, [occurrence_number])
- =REGEXREPLACE(text, regular_expression, replacement)
When we want to replace these characters based on their repetitions and occurrences we can use the substitute function, its formula takes the syntax below.
=SUBSTITUTE(A2,”, “, CHAR(10))
Step 1: The lengthy text is placed in cell A2.
Step 2: Type in the formula =SUBSTITUTE(A2,”, “, CHAR(10)) in cell A5.
Step 3: Click Enter and the occurrences of spaces and commas have been replaced with line breaks.
Similarly, the regexreplace function takes the syntax below.
=REGEXREPLACE(A2,”, “, CHAR(10))
Removing the Line Breaks In Google Sheets
The same way line breaks are added, they can also be removed. However, the formulas used to insert these line breaks would be in reverse syntax.
You can also remove them manually by deleting them, but in a case where there are lengthy texts in numerous cells on the worksheet, reverse formulas are preferred.
Using the SUBSTITUTE function stated earlier, we are going to switch some parameters in the formula.
Originally, we have =SUBSTITUTE(A2,”, “, CHAR(10)) but since we want to delete the new lines, the formula is retyped like this
=SUBSTITUTE( A5, CHAR(10),”, “)
The space and the comma are placed last in the formula, while the character function comes before them. Using the worksheet, where we replaced these characters, we would like to remove the inserted lines.
Step 1: In cell A8, input the formula =SUBSTITUTE( A5, CHAR(10),”, “).
Step 2: Click Enter and the inserted line breaks are removed. This entire procedure also applies to the REGEXREPLACE function.
Alternatively, you could use the Find and replace feature in the toolbar.
Step 1: Select the column or range of cells with line breaks.
Step 2: Click on Edit on the toolbar.
Step 3: From the list of features, click on Find and Replace.
Step 4: A pop-up is displayed asking what you want to find and replace in a selected range of cells. In the Find box, type in a comma and a space.
Step 5: In the Replace section, type in \r\n|\n|\r
Step 6: Under search, click on the drop-down arrow here. Click on Specific Range.
Step 7: There are five tick boxes with options. Select the first two options which are Match Case and Search using regular expression.
Step 8: Click on replace all.
Step 9: Click Done. All line breaks in the cells in the worksheet are removed.
Final Thoughts.
In this article, we share different methods of adding and removing the line break In google sheets. Now you know How to Insert a Line Break in Google Sheets.
Although there isn’t an inbuilt feature in Google Sheets, with the explained formulas, functions and shortcuts, inserting line breaks is as easy as pie. I hope you found this guide useful. Thanks.
