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–

• 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.

Read More: How to Split Text to Columns Using Formula in Google Sheets

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–

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

Read More: How to Split String into Array in Google Sheets (3 Easy Methods)

Similar Readings

• How to Split View in Google Sheets (2 Easy Ways)
• Split Cell by Comma in Google Sheets (2 Easy Methods)
• How to Split Address in Google Sheets (3 Easy Methods)
• Split a Cell in Google Sheets (9 Quick Methods)

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.

Read More: [Solved!] Split Text to Columns Is Not Working in Google Sheets

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 sheet = SpreadsheetApp.getActiveSheet();
  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.

Read More: How to Split String Using Apps Script in Google Sheets

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

• How to Split a Cell Diagonally in Google Sheets (3 Quick Ways)
• Use QUERY with SPLIT Function in Google Sheets
• How to Split Cells to Get Last Value in Google Sheets (4 Methods)
• Split Cell into Rows in Google Sheets (2 Useful Methods)