Text wrapping and new lines are two different types of cell formatting in Google Sheets. One of the benefits of using new lines over text wrapping is you can move the new lines in a cell to columns using a formula.

The Text Wrapping is a built-in feature which lets users wrap the values in a cell and thus restrict it overflowing into the adjoining cells.

The below image depicts what is text wrapping and new lines in a cell in Sheets.

Text Wrapping and New Lines in a Cell:

In this post I am elaborating on the following cell formatting related tips.

1. How to quickly add new lines in a cell in Google Sheets?
2. How to split/move new lines to columns in Sheets.
3. Clean new lines and insert a comma between strings.

How to Add New Lines in a Cell in Sheets

To add new lines in a cell, follow the below tips.

If the cell is blank;

Assume you want to add 5 new lines in cell A1. To do that double click the cell A1 (or hit the shortcut key F2). Then hit Alt+Enter 4 times.

If the cell has already a value;

If you want to move the existing text in cell A1 into new lines within that cell, double click the cell and move the cursor pointer to the position from which you want the new line to start. Then hit Alt+Enter.

Cleaning New Lines and Inserting Comma as Separator

You can reverse the new lines in a cell in Google Sheets using the CLEAN function as below.

=clean(D2)

I know it looks weird. You must replace the new line character with commas to make the text readable. That you can do as follows.

=SUBSTITUTE(D2,char(10),", ")

The SUBSTITUTE function replaces the new line character which is represented by the formula char(10) with a comma and an extra space. So the output will be as “Tracy, Matthew, Ada, Robin” which is obviously readable, right?

You can additionally include the CLEAN function with the above formula to ensure that other hidden characters, if any, are removed.

=clean(SUBSTITUTE(D2,char(10),", "))

How to Move New Lines to Columns in Google Sheets

To move the new lines to columns, use the SPLIT function.

=split(D2,char(10))

You can group items in a single cell using the new lines. Here is one example.

As you can clearly see, blank lines separate each group. You can move such groups to columns using a combination of formula. Here is that formula. The explanation follows.

=ArrayFormula(transpose(split(transpose(split(substitute(SUBSTITUTE(B4,char(10)&char(10),"|"),char(10),"^"),"|")),"^")))

Formula Logic/Explanation:

Step # 1:

The formula first substitutes the double new line (blank line generated using char(10) twice) with the pipe | symbol.

=SUBSTITUTE(B4,char(10)&char(10),"|")

Step # 2:

In the next step another substitution replaces the single new line with the caret ^symbol.

=substitute(SUBSTITUTE(B4,char(10)&char(10),"|"),char(10),"^")

Step # 3:

Time to split and transpose the text separated by the pipe symbol.

=transpose(split(substitute(SUBSTITUTE(B4,char(10)&char(10),"|"),char(10),"^"),"|"))

You will get the output as below.

Name of Person^Tracy^Matthew^Ada^Robin
Emp. Code^EMP1105^EMP1106^EMP1107^EMP1108
Basic Pay^450.00^450.00^400.00^425.00

Step # 4:

Split the text separated by the caret symbol and transpose again. That’s our final formula.

I hope I have well detailed on how to move new lines in a cell to columns in Google Sheets. Also, I have included some additional tips. If you find this tutorial useful, please do share. Thanks.

You May Like: How to Wrap Text Using Formula in Google Sheets [One Word Per Line].