If you have ever wanted to create paragraphs or lists in a cell using Google Sheets, you likely have needed to implement what is known as a line break or hard return. Often, people just use separate cells or use text wrapping to simulate the same effect, but there is actually a way to do create paragraphs or lists within individual cells. Show
It's very easy – just a simple key combination. When typing in the cell, key the combo: Alt + Enter or Control + Enter
Either works to create line breaks within a single cell in Google Sheets. See the GIF below. New posts sent to you weekly with one click!Join our mailing list to receive the latest news and updates from Instructional Tech Talk. We hate spam as much as you do, we will only send emails relevant to this website and never more than once a week. If you’ve ever tried to add a line break within a cell in Google Sheets, you know it’s not as easy as it is in a text editor. Simply hitting enter on your keyboard exits edit mode, instead of adding a line break. So what do you do if you want to add a line break in Google Sheets? Fortunately there are a couple of handy features you can use to quickly add a line break anywhere in your cells. Read on to learn more. Add a Line Break with Keyboard ShortcutsQuick Navigation The fastest way to add a line break is using the keyboard shortcut. This allows you to continue entering data without interrupting your flow. Here’s how: Step 1Select the cell you want to add a line break to and type in the text you want before the line break, then type CTRL+ENTER on your keyboard to insert a line break. Keep typing to add text after the line break. Step 2You can also add line breaks to cells that already have text in them. Simply double click on the cell to edit it then move your cursor to the desired location and CTRL+ENTER to insert the new line Add a Line Break with FormulasIn some cases, text has to be automatically generated using formulas, making the manual method of adding line breaks unusable. For these cases, the formula method can be used instead. Follow these steps to add line breaks to your formulas: Step 1For this example, we’ll use a simple text generating formula that counts the number of cells containing text in column A and outputs the number with a text label. The formula is: “=”Filled cells in Column A: ” & COUNTA(A:A)” Step 2To add line breaks to this formula, we combine the existing text with the CHAR(10) function, which generates a line break character. This function can be joined with any string using the standard string joining operator, the ampersand: &. So if we want to put the number calculated by the COUNTA() function on its own line, the formula would look like this: “=”Filled cells in Column A: ” & CHAR(10) & COUNTA(A:A)”. Step 3Note that you can add as many CHAR(10) functions to a formula as you want. For example, adding “& CHAR(10) & CHAR(10) &” to a formula will add 2 line breaks in a row, adding a blank line between paragraphs. SummaryExample Spreadsheet: Make a copy of the example spreadsheet In this tutorial, I covered how to line break in google sheets. Want more? Check out all the Google Sheets Tutorials. 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 SheetsWhile typing in a cell you can use a line break keyboard shortcut:
⌘ + 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 Sheet12 exclusive tips to make user-friendly sheets from today: Get The 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 SheetsIf your formula only contains "text", you can still use the shortcuts from above:
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: =CHAR(table_number) 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: A1I want line 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 A1I want line2breaks in3this text4I want line How To Add A New Line / Line Break In The Google Sheets iPhone & iPad AppsUnfortunately 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 A1I want line2breaks in3this text4I want line How To Add A New Line / Line Break In The Google Sheets Android AppUsing 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 : Replace Characters With Line Breaks In Google SheetsYou've got text that needs line breaks. Let's say it's a big column with data like this: A11725 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 SheetsYou'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: A11725 Slough Avenue How about the CLEAN function? =CLEAN(text) It removes all non-printable ASCII characters (which includes line breaks). In the example above you could use in cell B1: =CLEAN(A1) To get: AB11725 Slough Avenue 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: AB11725 Slough Avenue Much better. You could do even more by including a comma: =SUBSTITUTE(A1,CHAR(10),", ") AB11725 Slough Avenue FREE RESOURCE Google Sheets Cheat Sheet12 exclusive tips to make user-friendly sheets from today: Get The 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. You might like:How To Capitalize The First Letter In Google SheetsLearn step-by-step how to capitalize every word, the first word, or every sentence in a cell or column in Google Sheets. MAX Google Sheets Function [With Quiz]The MAX function in Google Sheets finds the maximum! It doesn't matter how many numbers, MAX will quickly find the largest / most positive. MINA Google Sheets Function [With Quiz]The MINA function in Google Sheets finds the minimum! Text or numbers... doesn't matter - MINA finds the smallest / most negative. How do I create a gap in Google Sheets?Open the Format menu. Hover over Number and select Custom Number Format from the very bottom of the list of options. Select the Custom Number Format box. Press Space to insert empty spaces.
How do I add a space between text in Google Sheets?To change your paragraph spacing, click Single, 1.15, 1.5, or Double. To change your spacing between paragraphs, click Remove space before paragraph or Add space after paragraph. To enter a custom size, click Custom spacing. Then, enter the size of the spacing you want before and after a paragraph and click Apply.
How do you break apart text in Google Sheets?Select the text or column, then click the Data menu and select Split text to columns... Google Sheets will open a small menu beside your text where you can select to split by comma, space, semicolon, period, or custom character. Select the delimiter your text uses, and Google Sheets will automatically split your text.
How do you insert a line break in a spreadsheet?To start a new line of text or add spacing between lines or paragraphs of text in a worksheet cell, press Alt+Enter to insert a line break. Double-click the cell in which you want to insert a line break (or select the cell and then press F2).
|