On Google Drive Spreadsheet How to Get to Continue to Next Line

Google Sheets Cheat Sheet

12 Exclusive Tips To More User-Friendly Sheets

You'll get updates from me with an easy-to-find "unsubscribe" link.

Line Breaks In Google Sheets [In Cells & Formulas]

To add a line break in a:

• Cell: use Ctrl + Enter (for Windows) or Ctrl + Return (for Mac)
• Formula: concatenate with the CHAR function (e.g. A1&CHAR(10)&A2 )

Adding a line break (or new line, or carriage return) in Google Sheets isn't obvious as just pressing Enter exits the cell.

Here's how to:

• add line breaks in a cell
• add line breaks in a formula
• add line breaks on mobile (iPhone/iPad & Android)
• replace characters with line breaks
• remove line breaks from text

You can get text to go across multiple lines in a cell without using line breaks by using text wrap formatting:

I've written a guide to text wrapping if you'd like to check it out.

How To Add A New Line / Line Break In A Cell In Google Sheets

While typing in a cell you can use a line break keyboard shortcut:

• Ctrl + Enter or Alt + Enter (for Windows)
• Ctrl + Return or Alt + Return or ⌘ + Return (for Mac)

⌘ + Return isn't the best option for Mac because it only lets you create one new line at a time.

Holding Ctrl or Alt is better as they let you press Return as many times as you need.

Here's what that looks like:

If you increase the height of one cell, the height of every cell in that row increases.

Using a line break might throw your sheet's formatting out of whack.

FREE RESOURCE

Google Sheets Cheat Sheet

12 exclusive tips to make user-friendly sheets from today:

You'll get updates from me with an easy-to-find "unsubscribe" link.

How To Add A New Line / Line Break In A Formula In Google Sheets

If your formula only contains "text" , you can still use the shortcuts from above:

• Ctrl + Enter or Alt + Enter (for Windows)
• Ctrl + Return or Alt + Return or ⌘ + Return (for Mac)

This is what it looks like:

If instead your formula refers to other cells and combines text from many cells into one you can concatenate multiple cells of text with an output from the CHAR function:

The CHAR function allows you to generate Unicode characters using their decimal reference in the current Unicode table.

This can get you lots of useful and unusual characters (including emojis) but for our purposes we need character 10 - a 'line feed'.

When used in a formula like this:

="I want line"&CHAR(10)&"breaks in"&CHAR(10)&"this text"

You get the output you need:

	A
1	I want line breaks in this text

You can also reference other cells instead of the text directly. In cell A4 we have the formula:

=A1&CHAR(10)&A2&CHAR(10)&A3

	A
1	I want line
2	breaks in
3	this text
4	I want line breaks in this text

How To Add A New Line / Line Break In The Google Sheets iPhone & 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 the iPhone and iPad:

In cell A4 we have the formula:

=A1&CHAR(10)&A2&CHAR(10)&A3

	A
1	I want line
2	breaks in
3	this text
4	I want line breaks in this text

How To Add A New Line / Line Break In The Google Sheets Android App

Using the Google Sheets Android app you can both type line breaks and add them in formulas.

To type a line break in text, you must press enter on the keyboard while at the end of a word:

The cell will be exited without a line break created if you press enter while not at the end of a word (e.g. after a space).

You can also use line breaks in formulas just as you would on the desktop app:

Replace Characters With Line Breaks In Google Sheets

You've got text that needs line breaks. Let's say it's a big column with data like this:

	A
1	1725 Slough Avenue, Scranton PA 18503

Addresses usually get split over multiple lines but going into each cell in the column and fixing this will take a really long time.

Let's write a formula to do the hard work quickly.

First let's use the SUBSTITUTE function:

=SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])

This searches the given text for something and replaces it with something else. It's powerful because you can even specify which occurrence to replace (the default is that all occurrences are replaced).

For the example:

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

You could also use the function:

=REGEXREPLACE(text, regular_expression, replacement)

It does the same thing as the SUBSTITUTE function but provides a great ability to specify what you're searching for. However, it is more difficult to specify which occurrence to replace.

For the example:

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

In most situations the SUBSTITUTE function will get you what you need and you don't have to worry about learning regular expressions.

Remove Line Breaks In Google Sheets

You've got line breaks but don't want them!

It'd be painful to go through and delete them all so let's write a formula to take care of it.

Here's your data:

	A
1	1725 Slough Avenue Scranton PA 18503

How about the CLEAN function?

It removes all non-printable ASCII characters (which includes line breaks).

In the example above you could use in cell B1 :

To get:

	A	B
1	1725 Slough Avenue Scranton PA 18503	1725 Slough AvenueScranton PA18503

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:

=SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])

To search_for a line break we'll use the handy CHAR function.

Using the example data, in cell B1 you could use:

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

To get:

	A	B
1	1725 Slough Avenue Scranton PA 18503	1725 Slough Avenue Scranton PA 18503

Much better.

You could do even more by including a comma:

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

	A	B
1	1725 Slough Avenue Scranton PA 18503	1725 Slough Avenue, Scranton PA, 18503

---

FREE RESOURCE

Google Sheets Cheat Sheet

12 exclusive tips to make user-friendly sheets from today:

You'll get updates from me with an easy-to-find "unsubscribe" link.

Kieran Dixon started using spreadsheets in 2010. He leveled-up his skills working for banks and running his own business. Now he makes Google Sheets and Apps Script more approachable for anyone looking to streamline their business and life.

You might like:

smithtexer1939.blogspot.com

Source: https://kierandixon.com/line-break-google-sheets/

Share this post