Have you ever tried adding a line break (or a new line, or a carriage return) in Google Sheets and found it wasn’t as simple as pressing Enter to exit the cell? Well, don’t worry, I’ve got you covered! Here’s how you can easily add line breaks in Google Sheets.
How to Add a New Line / Line Break in a Cell in Google Sheets
When typing in a cell, you can use a keyboard shortcut to insert a line break:
- Ctrl + Enter or Alt + Enter (for Windows)
- Ctrl + Return or Alt + Return or ⌘ + Return (for Mac)
Here’s what it looks like:
How to Add a New Line / Line Break in a Formula in Google Sheets
If your formula contains only “text”, you can still use the keyboard shortcuts mentioned above. However, if your formula references other cells and combines text from multiple cells into one, you can use the CHAR function to concatenate multiple text cells with a line feed output.
The CHAR function allows you to generate Unicode characters using their decimal reference in the current Unicode table. In our case, we need character 10 – a ‘line feed’.
When used in a formula like this:
= "Text1" & CHAR (10) & "Text2"
You’ll get the desired output:
Text1
Text2
You can also reference other cells instead of direct text. For example, in cell A4, we have the formula:
= A1 & CHAR (10) & A2
How to Add a New Line / Line Break in Google Sheets iPhone and iPad Apps
Unfortunately, you can’t type line breaks using the Google Sheets iOS app. You can’t even copy and paste line breaks into the app from other apps! However, you can still add line breaks in formulas on your iPhone and iPad.
In cell A4, we have the formula:
= A1 & CHAR (10) & A2
How to Add a New Line / Line Break in Google Sheets Android App
With the Google Sheets Android app, you can both type and add line breaks in formulas.
To type a line break in text, simply press Enter on the keyboard when you’re at the end of a word.
The cell will exit without creating a line break if you press Enter when you’re not at the end of a word (e.g., after a space).
You can also use line breaks in formulas the same way you do in the desktop app.
Replace Characters with Line Breaks in Google Sheets
Let’s say you have text that requires line breaks, like a large column of data with addresses spread across multiple lines. Going through each cell in the column to fix this would take a lot of time.
But fear not! Let’s write a formula to get the job done quickly.
Begin by using the SUBSTITUTE function:
= SUBSTITUTE (A1, "|", CHAR (10))
This function searches for the given text and replaces it with something else. It’s powerful because you can even specify which occurrence to replace (by default, all occurrences are replaced).
For example:
= SUBSTITUTE (A1, "|", CHAR (10), 2)
You can also use the REPLACE function:
= REPLACE (A1, 3, 1, CHAR (10))
It does the same thing as the SUBSTITUTE function but provides greater flexibility in specifying what you’re looking for. However, it’s more difficult to specify which occurrence to replace.
For example:
= REPLACE (A1, FIND ("&", A1), 1, CHAR (10))
In most situations, the SUBSTITUTE function will give you what you need, and you won’t have to worry about learning regular expressions.
Remove Line Breaks in Google Sheets
So, you have line breaks, but you don’t want them! Removing them one by one would be painful, so let’s write a formula to take care of it.
Here’s your data:
How to use the CLEAN function?
It removes all non-printable ASCII characters (including line breaks).
In the example above, you can use the following formula in cell B1:
= CLEAN (A1)
Result:
123 Rue du Commerce, Paris, France
Slight problem… the line breaks have been removed, but now there’s nothing in their place, making the address useless. Let’s try the SUBSTITUTE function instead:
To search for a line break, we’ll use the nifty CHAR function.
Using the example data, in cell B1, you can use:
= SUBSTITUTE (A1, CHAR (10), ", ")
Result:
123 Rue du Commerce, Paris, France
Much better!
You can do even more by including a comma:
= SUBSTITUTE (A1, CHAR (10), ", ")
Now you know how to add and remove line breaks in Google Sheets. These tricks will save you time and help you format your data in a neater and more readable way.
For more tips and tricks on Google Sheets and other useful tools, visit Crawlan.com.
