5 Ways to Insert a Line Break in Google Sheets

In this post, you’re going to learn about all the different ways you can insert a line break into your data inside a cell in Google Sheets.

Cells can contain a lot of characters. But as the number of characters in a cell increases, either the characters display over the adjacent cell if it’s blank, or the characters will be hidden from view if the adjacent cell isn’t blank.

If you’re working with only a few columns of data, this might not be much of a problem. But if your dataset is larger, then increasing the column width to accommodate the data might not be feasible.

Having a clear view of the contents of a cell at a glance is a must, but expanding the width or height of a cell might not be enough.

Sometimes, the most efficient way to make your cell contents visible, properly organized, and keep all your spreadsheet content on one screen view is to insert a line break.

There are several different ways to add a line break, so get your copy of the example workbook and follow along.

Insert a Line Break on Android or iOS Mobile App

This tip is handy for situations where you have to work on your phone. Inserting a line break when working from your phone is easy.

You just need to identify the position where you want to insert the line break and press the Enter key on your phone’s keyboard. You will get similar results just as if it’s done on a computer.

Note: Use the Gboard keyboard on your android device as the Enter key on other keyboards may not function the same.

Insert a Line Break with a Keyboard Shortcut

This method involves inserting a line break by using a keyboard shortcut.

Alt + Enter

Or

Ctrl + Enter

You can use either one of the keyboard shortcuts to insert a line break in a cell. Just follow these easy steps.

  1. Select the cell you want to insert the line break
  2. Double-click on the cell or press F2 so that you are in edit mode in the cell.
  3. Click on the position where you want to insert the line break. You can also use the arrow buttons on your keyboard to navigate to the desired location.
  4. Press Alt + Enter or Ctrl + Enter.

Depending on the contents of the cell, you can insert multiple line breaks inside a single cell using this method.

Insert a Line Break with Copy and Paste

This method requires that you make use of another software such as Notepad or any other text editor. But anyone you use should work just fine.

The process of copying and pasting is simple. What you need to do is to write out the items you want to put inside the cell and separate them by pressing Enter. All of this will be done inside of the text editor software.

After you do this, copy the items and paste them inside a cell in your spreadsheet.

Line breaks will be automatically inserted into the items due to the separation that has been done in the word document software using the Enter key.

You should note that when you’re pasting the items from the word document processor to your spreadsheet, you must double-click on the cell where you want to paste the items.

Doing this will make sure all the items are pasted inside a single cell.

Without double-clicking a specific cell to paste the items, they will be entered in separate cells as illustrated above.

Insert a Line Break with CHAR and UNICHAR Functions

There are two functions that you can use to insert a line break in Google sheets. They are the CHAR and UNICHAR functions.

Both of them are text functions and they take only one number argument which represents the character to insert in your spreadsheet.

Syntax for the CHAR Function

= CHAR ( number )
  • number is an integer number which represents the character to return.
= CHAR ( 10 )

The above formula will result in a line break character.

Syntax for the UNICHAR Function

= UNICHAR ( number )
  • number is an integer number which represents the character to return.
= UNICHAR ( 10 )

The above formula will result in a line break character.

You can use the CHAR and UNICHAR functions to insert line breaks by combining them with the ampersand & operator or by using them in the SUBSTITUTE function.

Using CHAR and UNICHAR with Ampersand Operator

The ampersand operator & can be used to combine or two or more text strings.

Text values in the formula are enclosed in quotation marks. This doesn’t change when combining characters using the ampersand.

When using the ampersand operator, you must ensure there are no characters between the syntax. The ampersand operator must come immediately after the value that’s going to be combined.

For instance, if you want to combine two values, the ampersand operator comes between both characters with no spaces before or after the ampersand.

This is illustrated by the formula used to derive the result of the combination in column B. If you want to include spaces, they must be enclosed in quotation marks and be in-between ampersand operator as seen in the formula used for columns C and D.

Understanding this concept will help you when you want to use the ampersand and the CHAR or UNICHAR functions to insert a line break inside a cell.

="Bob"&CHAR(10)&"Guy"&CHAR(10)&"Fred"&CHAR(10)&"Ana"&CHAR(10)&"Cynthia"

Using this syntax will insert line breaks in the specified locations between each name.

There are no stray spaces between the ampersand operators and the text values are enclosed in quotation marks.

Note: You can replace CHAR(10) with UNICHAR(10) in the syntax and you will get the same result.

Using CHAR and UNICHAR with the SUBSTITUTE Function

The SUBSTITUTE function is a much simpler alternative to the ampersand operator when looking to insert a line break.

However, it works best when your characters are separated with the same delimiter. This is because the SUBSTITUTE function works by finding a given text string and replacing it with another.

In this case, the CHAR or UNICHAR function will be used to replace a character through the SUBSTITUTE function.

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

The SUBSTITUTE function has four arguments – three compulsory arguments and one optional argument.

  • text_to_search: this specifies from which text the search is to occur.
  • search_for: this is where you specify what text you want the function to look for. This argument is case sensitive and must be enclosed in quotation marks.
  • replace_with: what to replace the search_for text with
  • occurrence_number: this argument is optional and refers to the number of times the search_for text appears in the text that’s being searched. This argument allows you to specify which occurrence of the search_for character to replace when it occurs multiple time.

For instance, In the word abracadabra, the letter a appears five times. If you don’t specify which occurrence of a to replace, all the a’s will be replaced.

But, if you want to replace only the first a in the word, you will enter 1 in the occurrence_number argument and only the first a will be replaced.

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

Copy and paste this formula to insert a line break in the cell.

With these formula solutions, you can easily insert line breaks to multiple cells by using the fill handle to copy down the formula.

Insert a Line Break with Apps Scripts

You can also use Google Apps Scripts to automate the process of replacing delimiter characters with line breaks.

Go to the Extension menu and select Apps Script from the options to open the script editor.

You can paste in the below code.

function onEdit(e) {
  if (SpreadsheetApp.getActiveSpreadsheet().getSheetName() == 'Apps Scripts') {
    if (typeof e.value != 'object') {
      e.range.setValue(e.value.replace(", ", "\n"));
    }
  }
}

The code will replace any occurrence of a comma and space character with a line break.

The neat thing is this code will run automatically every time you edit the spreadsheet. But it will only replace the characters in a sheet named Apps Scripts.

As you enter data into the spreadsheet, you will see the values automatically update with the line breaks!

Conclusion

Line breaks are great solutions for arranging text more concisely in your cells. It also ensures that all your data appears in full view and isn’t cut off when it’s too long.

The manual keyboard shortcuts for adding a line break inside a cell are great when you only want to insert a single line break.

Otherwise, using either one of the formulas or Apps Scripts will be the most efficient way to insert line breaks in a cell.

What other tricks do you use for inserting a line break? Let us know in the comments section below!

About the Author

Oluwaseun Olatoye

Oluwaseun Olatoye

Oluwaseun is a business intelligence analyst with expertise in Google Sheets and SQL programming language. He has worked with various businesses to make data-driven decisions. He enjoys helping others learn and grow.

Related Articles

Comments

0 Comments

Submit a Comment

Your email address will not be published.

Get the Latest Google Sheets Tips

Write For Us

Are you a tech enthusiast with a talent for writing great content? Come write for us!

Follow Us

Follow us on social media to stay up to date with the latest in Google Sheets!