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

Google Sheets Cheat Sheet

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:

shows that you can use text wrap formatting in a cell to force line breaks without having to add them in the actual text

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:

shows how to add line breaks in a cell that contains text using keyboard shortcuts

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.

hand pointing emoji hand pointing emoji

FREE RESOURCE

Google Sheets Cheat Sheet

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

Google Sheets Cheat Sheet

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:

shows how to add line breaks in a formula using keyboard shortcuts

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
shows how to add line breaks using formulas in the google sheets iphone and ipad apps

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:

shows how to add line breaks while typing text in the google sheets android app

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:

shows how to add line breaks using formulas in the google sheets android 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

hand pointing emoji hand pointing emoji

FREE RESOURCE

Google Sheets Cheat Sheet

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

Google Sheets Cheat Sheet

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.

smithtexer1939.blogspot.com

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

0 Response to "On Google Drive Spreadsheet How to Get to Continue to Next Line"

Postar um comentário

Iklan Atas Artikel

Iklan Tengah Artikel 1

Iklan Tengah Artikel 2

Iklan Bawah Artikel