# How to Split Text to Columns Based on Line Break in Google Sheets

With Google Sheets’ Split text to columns based on line break feature, you can break up text into numerous cells based on where lines break. When a cell has numerous lines of text and you wish to break each line out into its own independent cell for additional analysis or modification, this feature is helpful. This feature splits the text at each line break to make each line of text into a distinct entry in a new column. In this article, I’ll demonstrate 4 useful ways to split a text into columns based on line breaks in Google Sheets. Here is an overview of what we will archive:

## 4 Useful Ways to Split Text to Columns Based on Line Break in Google Sheets

We will use the dataset below to demonstrate 4 useful ways to split a text into columns based on line breaks in Google Sheets. Here, the dataset contains information on each customer in each cell, such as name, address, city, and zip code. Now, we’ll divide the text based on line breaks so that the columns for the name, address, city, and zip code are separated.

### 1. Combining SPLIT and CHAR Functions

One way to split a text into columns based on line breaks in Google Sheets is to use the combination of the SPLIT and CHAR functions. This is one of the simplest ways to split a text. The advantages of employing these functions include the ability to divide the text into more than one column, the ability to define where the split should occur within the text, and the ability to split text based on characters or sequences other than line breaks.

Steps:

• Firstly, select a cell where you want to apply the formula. In our case, we selected Cell C5. Next, enter the formula below and press Enter
`=SPLIT(B5,CHAR(10))`

Formula Breakdown

• CHAR(10)

In Google Sheets, the function CHAR(10) generates a new line inside a cell. It enables you to show text across several lines in a single cell.

• SPLIT(B5,CHAR(10))

Here, the SPLIT function in Google Sheets divides text in Cell B5 into separate values based on a delimiter specified by the CHAR(10) function.

• As a result, it will split the text in Cell B5 based on line breaks in the cell. Now, to apply the formula to the remaining cell, drag the Fill Handle icon downward.

• Thus, it will split all the cells in Column B into multiple columns.

### 2. Joining ARRAYFORMULA, IFERROR, SPLIT, and CHAR Functions

We can also combine the ARRAYFORMULA, IFERROR, SPLIT, and CHAR functions to split the text in a cell into multiple columns based on line breaks in the text. By using the ARRAYFORMULA function, we can use a single formula for all the cells to split text. Here, the IFERROR function helps improve the stability and reliability of your spreadsheet by preventing errors from propagating and interfering with the remaining computations.

Steps:

• Firstly, choose a cell where you’re going to apply the formula to split the text. In this instance, we chose Cell C5. Next, input the formula below and hit Enter
`=ARRAYFORMULA(IFERROR(SPLIT(B5:B8,CHAR(10)),""))`

Formula Breakdown

• CHAR(10)

Firstly, the CHAR(10) function in Google Sheets creates a new line inside a cell.

• SPLIT(B5:B8,CHAR(10))

Here, the SPLIT function in Google Sheets splits text in the range Cells B5:B8 into separate values based on the delimiter specified by the CHAR(10) function.

• IFERROR(SPLIT(B5:B8,CHAR(10)),””)

Afterward, the IFERROR function will return the value of the output returned by the SPLIT function if there is no error in the cell. Otherwise, it will return a blank cell.

• ARRAYFORMULA(IFERROR(SPLIT(B5:B8,CHAR(10)),””))

Here, the ARRAYFORMULA function in Google Sheets applies a formula to a range of Cells B5:B8, rather than just a single cell.

• As a result, it will divide all of Column B‘s cells into different columns.

### 3. Using Split Text to Columns Command

With the use of the Split Text to Columns tool in Google Sheets, you may divide a cell’s contents into various columns. For instance, you can use this tool to divide a cell containing a person’s name and address into two distinct columns: one for the name and one for the address. This makes your data easier to comprehend and analyze. Assume that each customer’s details, including name, address, city, and zip code, are listed in a separate cell in our dataset with commas separating each line.

Steps:

• Firstly, select all the cells for which you want to apply the Split Text to Columns In our case, we selected Cell B5:B8. Next, go to the Data tab from the top menu bar and select Split text to column.

• As a result, a short dialog box titled Separator will appear on your screen. Next, you have to choose the correct delimiter that will split the text into multiple columns. So, click on the drop-down list of the Separator.

• Then, choose a correct delimiter based on how you want to split the text. In our case, we chose Comma as each line is separated with a comma operator.

• Thus, it will immediately split all the selected cells into multiple columns based on the comma separator which indicates each line break here.

### 4. Applying Apps Script

A large spreadsheet with hundreds or thousands of rows might take a lot of time to split the cells in each row manually. However, you may create a script using Google Apps Script that will execute the split automatically. It will save your time and work. Additionally, you may change the script to handle cells with various numbers of items or to provide alternative delimiters for certain cells.

Steps:

• Firstly, select all the cells for which you want to execute the script. In our case, we selected Cell B5:B8. Next, go to the Extensions tab from the top menu bar and select Apps Script.

• As a result, it will open a weblink in your browser like the following.

• Now, in the Execution log, enter the following script to split the text in a cell into multiple columns.
```function SplitTextToColumnsLineBreak() {
var range = sheet.getActiveRange();
var values = range.getValues();
var newValues = [];
for (var a = 0; a < values.length; a++) {
for (var b = 0; b < values[b].length; b++) {
var splitValues = values[a][b].split("\n");
newValues.push(splitValues);
}
}

var numRows = values.length;
var numCols = newValues[0].length;
var newRange = sheet.getRange(range.getRow(), range.getColumn(), numRows, numCols);
newRange.setValues(newValues);
}```

• Next, we have to save the script. For this, click on the Save project icon.

• Then, run the project by clicking Run the selected function icon.

• As a result, you will receive a message named Execution completed.

• Now, go back to your dataset. You will find all the selected cells are divided into multiple columns based on line breaks.

## Conclusion

This concludes our article. In this article, I’ve covered 4 useful ways to split a text into multiple columns based on line breaks in Google Sheets. I hope this will meet your requirements. Please feel free to leave any queries or suggestions in the comment section below. To explore more of these informative articles on Google Sheets, visit our site Officewheel.com.

## Related Articles

#### Sishir Roy

Hello, I'm Sishir Roy. I currently work for the SOFTEKO company at officewheel.com as a technical writer and content developer. I completed my BSc degree in civil engineering from BUET. I like using my talents as a creative problem-solver and thinker to come up with innovative solutions to problems. I have a great interest in the research and development of data science. Please follow my blog at Officewheel.com for more Google Sheets solutions.

We will be happy to hear your thoughts