How to Split String Using Apps Script in Google Sheets

We often require to split appended values from a cell. For example, we can separate the first and last names from a list of full names, divide URLs and email extensions or extract known values from a string by performing the split operation. There is a function and a feature that can execute the split operation. However, in this article, I’ll demonstrate how you can use the Google Apps Script to split a string in Google Sheets. We’ll discuss 3 ideal examples of splitting a string and endow the script required for executing a split operation. The following image contains an overview of the result attained after implementing the required script.

A Sample of Practice Spreadsheet

You can copy our practice spreadsheets by clicking on the following link. The spreadsheet contains an overview of the datasheet and an outline of the demonstrated examples to split any string using a script in Google Sheets.

3 Ideal Examples to Split String Using Apps Script in Google Sheets

First, let’s look at the dataset we’ll use for most of this article. The dataset contains a list of names that we want to split into two different cells. Now, let’s start.

1. Splitting Strings Containing Equal Number of Parts

First, we’ll consider splitting strings that contain an equal number of parts. Every full name in our dataset contains only two parts (first name and last name). Now, let’s write a script and implement it to split the full names into first names and last names. Basically, we’ll create a function using Apps Script to split the strings.

Steps:

• From the worksheet where you want to implement the script, go to the Extensions ribbon and select Apps Script from the appeared options.

• At this point, you will have a new window like the following in your browser where you can write the script.

• Now, rename the project and file.
• Afterward, insert the following script-

function splitDataEqual() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var last_row = ss.getLastRow();
  var last_col = ss.getLastColumn();
  for (var i=5; i<=last_row;i++){
    var fullName = ss.getRange(i,2).getValue();
    var partName = fullName.split(" ");
    for(var j=3;j<=last_col;j++){
      var req_info = partName[j-3];
      ss.getRange(i,j).setValue(req_info);
    }
  }
}

• Then, Save and Run the project to execute the split operation. Provide all the required accesses for authorization.

• Now, as soon as the file execution is completed, return to the Google Sheets window to check the results.
• As we can see, the full names have been split into first names and last names for the range.

Read More: How to Split Cells to Get Last Value in Google Sheets (4 Methods)

2. Separating Strings Containing Unequal Number of Parts

Now, let’s consider a little complex scenario. In the dataset below we have a list of full names where the strings don’t have an equal number of parts. Some strings contain first, middle, and last names. Whereas, some strings also have extensions to their first, middle, and last names. On the contrary, some strings consist of only first and last names. So, how can we perform a split operation for this dataset? Keep reading to learn how.

Steps:

• First, open the Apps Script window from the Extensions ribbon.
• Since a script is already present, click on the Add a File icon first and then select Script from the two options to open a new file.

• After a new file opens, rename the file and insert the following script-

function splitDataUnequal() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var last_row = ss.getLastRow();
  var last_col = ss.getLastColumn();
  var nameparts =[];
  var req_info;
  for (var i=5; i<=last_row;i++){
    var fullName = ss.getRange(i,2).getValue();
    var partName = fullName.split(" ");
    if (partName.length >= 3){
      [nameparts[0],nameparts[1],...nameparts[2]] =partName;
      for(var j=3;j<=last_col;j++){
        if(j<last_col){
          req_info = nameparts[j-3];
        }
        if (j==last_col){
          req_info = nameparts[j-3].join(" ");

        }
        ss.getRange(i,j).setValue(req_info);
      }
    }
    else{
      nameparts[0] = partName[0]
      nameparts[1] = "-"
      nameparts[2] =partName[1];
      for (var k=3;k<=last_col;k++){
        req_info = nameparts[k-3];
        ss.getRange(i,k).setValue(req_info);
      }
      }
    }
  }

• Now, Save and Run the file. We authorized the permissions in the previous example. Hence, we don’t require authorization again in this example.

• After the file execution is completed, return to the Google Sheets window to check the output. We have got the following output after the execution of the script.

• Here, the names containing first, middle, and last names have been split into the required cells.
• For the names which contain an extension, the extension is appended with the last name.
• And for the names that don’t consist of a middle name, we have put a hyphen (“–”) symbol to depict its absence.

Read More: How to Split a Cell in Google Sheets (9 Quick Methods)

3. Dividing Strings Around Multiple Delimiters

The dataset for the previous two examples contained only one delimiter. So you may wonder how can we split strings that contain multiple delimiters. Let’s have a look at the following dataset. Every cell of Column B contains the first name, last name, profession, and accommodation region of a person separated by space (“ ”), hyphen (“-”), and comma (“,”) delimiters. Now let’s write a script to execute the split operation here.

Steps:

• Follow the first two steps of the previous method to open a new script file using the Add a File icon first and rename the file afterward.
• Now, insert the following script-

function splitDataMultipleDelimiter() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var last_row = ss.getLastRow();
  var last_col = ss.getLastColumn();
  var search = ["-",","];
  var r = [" "];
  ss.getDataRange().createTextFinder(search.join("|")).
  useRegularExpression(true).replaceAllWith(r);
    for (var i=5; i<=last_row;i++){
      var fullName = ss.getRange(i,2).getValue();
      var partName = fullName.split(" ");
      for(var j=3;j<=last_col;j++){
        var req_info = partName[j-3];
        ss.getRange(i,j).setValue(req_info);
      }
  }
}

• At this point, Save and Run the file.

• After the file execution is complete, the dataset now looks like the following.
• As we can see, the string is split into the required cells. However, the source strings that were split have also changed. Instead of multiple delimiters, only one delimiter is present now. This is because the script tokens for split operation can deal with only one delimiter. Hence, we replaced every other delimiter with a single delimiter.

Read More: How to Split Cell by Comma in Google Sheets (2 Easy Methods)

Alternatives of Using Apps Script to Split String in Google Sheets

In the previous section, we used scripts to split strings in Google Sheets. There are a couple of functions and features to perform a similar operation without a script. Keep reading to learn about the implementation of these alternatives.

1. Applying Split Text to Columns Feature

The first alternative we want to demonstrate is the Split Text to Columns feature. The Split Text to Columns feature can split strings of a range around a delimiter. Follow the simple steps below to execute this feature.

Steps:

• Firstly, select the range B5:B10 and copy it by using the keyboard shortcut CTRL+C.

• Now, select Cell C5 and use the keyboard shortcut CTRL+V to paste the copied range. At this time, the clipboard for paste formatting will be visible. Click on the clipboard menu.

• As soon as you click on the clipboard menu, a list of options will appear. Select the Split Text to Columns feature from the list.

• Another way to select the Split Text to Columns feature is to select the C5:C10 range first and then go to the Data ribbon to select the Split Text to Columns feature next.

• After selecting the Split Text to Column feature, a clipboard like the following will appear where you can choose the separator around which the split operation will be executed.

• Click on the Separator clipboard to generate a list of separators and select Space from the list. You may choose the separator you require. You can also insert a custom separator if it isn’t available in the appeared list.

• As soon as you select the Space option as the separator for the Split Text to Columns command, the copied values will be split into required columns.

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

2. Employing SPLIT Function

Another alternative to implementing a script for splitting strings is to employ the SPLIT function. It is an exclusive function for Google Sheets and allocates values in adjacent cells of a row around a delimiter.

Steps:

• Firstly, activate Cell C5 by double-clicking on it.
• Then, type in the following formula-

• Afterward, press the Enter key to get the required output.

• Now, select Cell C5 again and then hover the mouse pointer above the bottom-right corner of the selected cell.
• The Fill Handle icon will be visible at this time.
• Finally, use the Fill Handle icon to copy the formula to other cells of Column C.

• The final output looks like the following after using the Fill Handle tool.

Read More: How to Use QUERY with SPLIT Function in Google Sheets

3. Combining SPLIT and REGEXREPLACE Functions

In the 3rd example of the previous section, we performed a split operation for strings with multiple delimiters by implementing a script. The source range was also modified after the implementation of the script. If you want to keep the source range unchanged, you can combine the SPLIT and REGEXREPLACE functions.

Steps:

• Firstly, select Cell D5.
• Afterward, insert the following formula-

• Later, get the required output by pressing the Enter key.
• Finally, Finally, use the Fill Handle icon to copy the formula to other cells of Column D.

• The final output looks like the following after using the Fill Handle tool.

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

Things to Be Considered

• You have to be in the sheet where you can the script to be implemented to make it the active sheet for your script. Otherwise, you need to get your sheet by name for the proper execution of your script.

Conclusion

This concludes our article to learn how to split a string using Apps Script in Google Sheets. I hope the demonstrated examples were ideal for your requirements. Feel free to leave your thoughts on the article in the comment box. Visit our website OfficeWheel.com for more helpful articles.

Related Articles

• Split Text to Columns Based on Line Break in Google Sheets
• How to Split a Cell Diagonally in Google Sheets (3 Quick Ways)
• Split Cell into Rows in Google Sheets (2 Useful Methods)
• How to Split View in Google Sheets (2 Easy Ways)
• [Solved!] Split Text to Columns Is Not Working in Google Sheets
• How to Split Address in Google Sheets (3 Easy Methods)