This tutorial explains how to move new lines in a cell to columns or rows in Google Sheets. Before that, let’s understand what a new line is and how to add or remove them.
Introduction: Understanding New Lines
Text wrapping and new lines are two different ways of formatting text in Google Sheets. One advantage of using new lines instead of text wrapping is that you can move new lines in a cell to columns using a formula.
Text Wrapping is a built-in feature that allows users to wrap text within a cell, preventing it from overflowing into adjacent cells.
How to Add New Lines in Google Sheets
If you want to insert a new line within a cell, double-click the cell, place the cursor at the desired position, then press:
- Windows:
Alt + Enter - Mac:
Option + Enter
How to Remove New Lines in Google Sheets
To remove new lines from a cell, use the CLEAN function, not the TRIM function.
=CLEAN(D2)
If the new lines were inserted without spaces, the output may look cluttered. To make the text more readable, replace new line characters with spaces using the SUBSTITUTE function:
=SUBSTITUTE(D2, CHAR(10), " ")This replaces each new line character (represented by CHAR(10)) with a space.
Moving New Lines to Columns or Rows in Google Sheets
Google Sheets provides a “Split text to columns” option under the Data menu, but it doesn’t support new lines (CHAR(10)) as a delimiter. Since there’s no built-in way to split text by new lines using that method, we must use formulas instead.
To move new lines to columns, use the SPLIT function:
=SPLIT(D2, CHAR(10))If you want to move new lines to rows, combine TOCOL with SPLIT:
=TOCOL(SPLIT(D2, CHAR(10)))These formulas allow you to restructure data by breaking multi-line values into separate columns or rows.
Moving New Lines to Columns While Keeping Groups
Sometimes, new lines within a cell are used to separate groups of data with an empty line in between. If you want to preserve these groups while moving new lines to columns, use the following formula:
=ArrayFormula(TRANSPOSE(SPLIT(TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(B4, CHAR(10)&CHAR(10), "|"), CHAR(10), "^"), "|")), "^")))
How This Formula Works
- Replace Double New Lines
The formula first replaces consecutive new lines (empty lines) with a pipe (|) symbol.=SUBSTITUTE(B4, CHAR(10)&CHAR(10), "|") - Replace Single New Lines
Next, it replaces single new lines with a caret (^).=SUBSTITUTE(SUBSTITUTE(B4, CHAR(10)&CHAR(10), "|"), CHAR(10), "^") - Split and Transpose
The formula then splits the text at each pipe (|) symbol and transposes the results.=TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(B4, CHAR(10)&CHAR(10), "|"), CHAR(10), "^"), "|")) - Final Split & Transpose
Finally, it splits the text at each caret (^) symbol and transposes it again, converting new lines into separate columns while preserving groups.
Conclusion
This tutorial covered how to move new lines in a cell to columns and rows using Google Sheets formulas. We also explored handling grouped data while moving new lines.
If you found this guide helpful, please share it. Thanks for reading!
