Data Analytics Full Course 2024 | Part 03 | Intermediate Excel

The GPT function in Google Sheets is not a built-in function like TEXTSPLIT, but you can create a custom function using Google Apps Script to mimic similar functionality.

Here’s how you can create a GPT function in Google Sheets to split text based on delimiters:

  1. Open Google Sheets.
  2. Go to Extensions > Apps Script.
  3. Delete any code in the script editor and paste the following script:
function GPT(text, col_delimiter, row_delimiter) {
  // Check if the text is provided
  if (text == undefined) {
    return [['']];
  }
  
  // Split the text into rows
  var rows = text.split(row_delimiter);
  
  // Split each row into columns
  var result = rows.map(function(row) {
    return row.split(col_delimiter);
  });
  
  return result;
}
  1. Save the script with a name like “GPTFunction”.

How to Use the Custom GPT Function

The syntax for using the GPT function you created will be:

=GPT(text, col_delimiter, row_delimiter)

Example Usage:

Example 1: Splitting text into columns

Suppose you have the text string “Apple, Banana, Cherry” in cell A1 and you want to split it into separate columns based on the comma delimiter.

=GPT(A1, ", ", ";")

This will result in:

Apple     Banana     Cherry

Example 2: Splitting text into rows

If you have a text string “Apple; Banana; Cherry” in cell A1 and you want to split it into separate rows based on the semicolon delimiter.

=GPT(A1, ",", "; ")

This will result in:

Apple
Banana
Cherry

Example 3: Splitting text into both rows and columns

For a more complex scenario, if you have a text string “Apple, Banana; Cherry, Date” in cell A1 and want to split it into both rows and columns using commas for columns and semicolons for rows:

=GPT(A1, ", ", "; ")

This will result in:

Apple     Banana
Cherry    Date

Notes:

  • Error Handling: The custom function includes a simple check to handle cases where the input text is undefined, returning an empty cell in such cases.
  • Flexibility: You can further customize the script to handle more complex scenarios or additional features such as ignoring empty cells or trimming spaces.

This custom function in Google Sheets should help you achieve similar functionality to the TEXTSPLIT function in Excel, allowing you to split text based on specified delimiters.

Resources

Download Dataset