How to use Split Text to Columns in Google Sheets

Let’s say you work in a customer service department and receive a list of customer names and addresses all combined into one column in your Google Sheets. You need to separate the names and addresses into different columns to better manage your data, which is where the “Text to Columns” option comes in handy.

In the above data, we have a single column where each set of details is separated with a comma. Now, we need to split these details into multiple columns. Also, if you have first and last names in a single column and want to separate these names, you can use the text-to-column option.

When you get data from a CSV to Google Sheets, you often get all the data in a single column.

Note: Always create a backup of your original file before splitting it, and unhide rows if there are some hidden rows in your data.

Split a Single Column into Multiple Columns using Text to Column

As I have said, in Google Sheets, text-to-columns is a powerful option that allows users to split the data of a single column into multiple columns based on a specified delimiter, such as a comma, space, or any other character.

1. First, select the column with the data you want to split. To do this, click on the column header to select all the cells in that column.
   
2. Next, go to the menu bar and click on the “Data” menu. Click on the “Split text to columns” from the drop-down menu. When you click on it, Google Sheets will try to detect the delimiter from the column and split the data based on that. But it will also show you a small drop-down where you can select the delimiter.
   
3. From that drop-down, you can select the delimiter. We need to use the comma (,) as a delimiter. And you can see when I select the comma from the drop-down, it shows me three separate columns (adjacent columns).
   
4. In the end, you can use autofit to adjust the width of the columns according to the data you have.
   

You can also select a custom delimiter from the drop-down to split the column into multiple columns.

Note – If a worksheet or cells are locked in Google Sheets, you won’t be able to use the “Split text to columns” option on those cells. If you try to apply this feature on locked cells, Google Sheets will display an error message or not allow the action. If you have a merged cell in the data, this option will split data and make the split cells also merge with the data separated.

Read Also – Concatenate String/Text with Separator in Google Sheets

Using SPLIT Function to Separate Data into Multiple Columns

The SPLIT function splits text into multiple columns based on a specified delimiter in a column. This function is handy when you have data in a single column that you want to separate into different columns.

SPLIT(text, delimiter, [split_by_each], [remove_empty_text])

• text: The text string to split.
• delimiter: The character or characters to use as a delimiter to split the text. If you want to split data based on more than one delimiter, you can add both in the same argument.
• split_by_each (optional): Whether to split by each character in the delimiter. The default is TRUE.
• remove_empty_text (optional): Whether to remove empty text values. The default is TRUE.

Using the same data you have, you can use the SPLIT to separate columns based on a comma.

Let’s break down the formula to understand it.

The formula separates the value in cell A1 into multiple columns based on a specific separator, which in this case is a comma (“,”).

In cell A1, you have a combined text containing a name, address, and email: “John Doe, 123 Elm St, johndoe@email.com.”

When you use this formula, it looks for each occurrence of “,” in the text and splits it into separate cells. So, “John Doe” goes into cell B1, “123 Elm St” into cell C1, and “johndoe@email.com” into cell D1.

When you have a different delimiter, as in the following example, you can use a formula I have used.

In the above example, we have data where each entry in the column uses different delimiters within the same cell. Now, you need to split this data from a single column to multiple columns.

To split these entries into separate columns, you can combine the SPLIT function and other functions like ARRYAFORMULA and TRIM for splitting.

First, the SPLIT function takes the text in cells A1 to A10 and splits it wherever it finds either a hyphen (“-“) or a colon (“:”). It separates the names, addresses, and email addresses into different parts.

Next, the TRIM function removes any extra spaces from the beginning and end of each text, ensuring everything is clean and neat.

Finally, the ARRAYFORMULA function ensures that the entire operation applies to all the cells from A1 to A10 simultaneously rather than one cell at a time. This way, column A’s names and email addresses are split into columns B, C, and D, respectively, with any extra spaces trimmed off.

Read Also – Combine Text From Two Cells in Google Sheets

Use a Google App Script to Split Column in Multiple Separate Columns

This Google Apps Script splits the data into multiple columns in a specified column using a specified delimiter. It assumes the data is in the first column (Column A) and will split it into multiple columns.

function splitColumnToMultipleColumns() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // Get the active sheet
  var range = sheet.getRange("A1:A" + sheet.getLastRow()); // Adjust the range as needed
  var values = range.getValues();
  var delimiter = "-"; // Change this to your delimiter, e.g., "," or ":"
  
  for (var i = 0; i < values.length; i++) {
    var splitValues = values[i][0].split(delimiter); // Split the cell value by the delimiter
    for (var j = 0; j < splitValues.length; j++) {
      sheet.getRange(i + 1, j + 2).setValue(splitValues[j].trim()); // Place each split value in adjacent columns and trim spaces
    }
  }
  
  SpreadsheetApp.getActiveSpreadsheet().toast('Split complete!', 'Status');
}

Let’s say you have data in column A, and when you run this code, it splits it into three adjacent columns, B, C, and D, using the “-” delimiter. You can change this delimiter from the code and modify it how you want.

And here’s a code that uses multiple delimiters (-:)

function splitColumnToMultipleColumns() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // Get the active sheet
  var range = sheet.getRange("A1:A" + sheet.getLastRow()); // Adjust the range as needed
  var values = range.getValues();
  
  // Define the delimiters
  var delimiters = ["-", ":"];

  for (var i = 0; i < values.length; i++) {
    var cellValue = values[i][0];
    var splitValues = [cellValue];

    // Split using each delimiter
    delimiters.forEach(function(delimiter) {
      splitValues = splitValues.flatMap(function(value) {
        return value.split(delimiter);
      });
    });
    
    // Place each split value in adjacent columns and trim spaces
    for (var j = 0; j < splitValues.length; j++) {
      sheet.getRange(i + 1, j + 2).setValue(splitValues[j].trim());
    }
  }
  
  SpreadsheetApp.getActiveSpreadsheet().toast('Split complete!', 'Status');
}

To use these codes, you can go to the Extensions, click “App Script” to open the script window, and then paste it there. Then save it and click the “Run” button to use the code.

Read Also – Extract Substring in Google Sheets