[GAS Beginner Series #17] Master String Manipulation: How to Use String.split() and String.replace()

Have you ever opened a customer management list or form responses in Google Sheets only to struggle because full addresses are lumped into a single cell, making it impossible to aggregate data by city or district? Or perhaps phone numbers are formatted inconsistently—some with hyphens and others without?

Fixing a handful of entries manually isn’t difficult, but when dealing with hundreds or thousands of rows, editing cells one by one quickly becomes overwhelming. In programming, standardizing and cleaning up messy data is known as “data cleansing”—an essential, unavoidable process in real-world workflows.

In this guide, we’ll break down two super-powerful Google Apps Script (GAS) methods that make text processing effortless: String.split() (for splitting strings) and String.replace() (for replacing strings). We’ll cover how they work in full detail with beginner-friendly explanations!

Once you master these two methods, your scripts will automatically transform messy text into clean, structured data. We will also dive into common pitfalls beginners run into along with their complete solutions, so let’s get started!

1. What Is String.split()? (Splitting Strings)

String.split() is a method that divides a target string by a specified delimiter and returns the elements as an array (a group of data).

✂️ Think of Cutting a Ribbon with Scissors

Imagine a long ribbon printed with several star (“★”) patterns along its length.

Calling split("★") is just like telling your computer: “Take a pair of scissors and snip the ribbon everywhere you see a ★!” The resulting ribbon pieces become a collection of shorter ribbons (an array).

Basic Syntax

var newArray = originalString.split("delimiter_character");

[Hands-on 1] Let’s Split Comma-Separated Values

For example, suppose a single spreadsheet cell contains “apple,orange,grape” separated by commas (,). If you want to separate each fruit into individual items, you write:

function splitFruits() {
  var fruitsText = "apple,orange,grape";
  
  // Use the comma (,) as scissors to split the string!
  var fruitsArray = fruitsText.split(","); 
  
  Logger.log(fruitsArray); 
  // Execution result: [ 'apple', 'orange', 'grape' ]
}

💡 Crucial Point: Split Results Are Stored in an “Array”!

After running split(), the resulting data is no longer a plain string; it is stored inside an array enclosed in square brackets [ ].

Remember the zero-based indexing rule for arrays covered in Series #13 and #15:

  • fruitsArray[0] ➡ “apple”
  • fruitsArray[1] ➡ “orange”
  • fruitsArray[2] ➡ “grape”

Once separated into an array, you have full control to perform operations such as extracting only the second fruit.

2. What Is String.replace()? (Replacing Strings)

String.replace() is a method that locates a specific character or substring within a target string and swaps it with a new specified value.

🩹 Think of Applying Correction Tape and Rewriting

When you spot a typo on a paper document, you cover it with correction tape and write the correct text on top. replace() automates this exact process.

If you simply want to delete unwanted characters, you can set the replacement text to an empty string (nothing).

Basic Syntax

var newString = originalString.replace("target_character", "replacement_character");

[Hands-on 2] Let’s Replace Date Separators

Let’s replace the first slash in the slash-delimited date string “2026/09/22” with a hyphen (-).

function replaceDate() {
  var dateText = "2026/09/22";
  
  // Replace the first slash (/) with a hyphen (-)
  var newDateText = dateText.replace("/", "-"); 
  
  Logger.log(newDateText);
  // Execution result: 2026-09/22
}

[Hands-on 3] Let’s Delete Unwanted Characters

When phone numbers contain hyphens (-), you might need only digits to register them into an external system. In that scenario, specify "" (two double quotes = an empty string) as the replacement character.

function cleanPhoneNumber() {
  var phoneNum = "090-1234-5678";
  
  // Replace hyphen (-) with an empty string ("") — effectively deleting it!
  var cleanNum = phoneNum.replace("-", ""); 
  
  Logger.log(cleanNum);
  // Execution result: 0901234-5678 (*Notice only the first hyphen was removed! More on this below.)
}

3. Practical Use Case: Combining Both Methods to Extract Data

Now let’s combine split() and replace() to handle a realistic text-cleansing task. Here, we’ll write a script that takes a Japanese address string from a spreadsheet—like “北海道札幌市中央区大通西1丁目…” (Hokkaido Sapporo-shi Chuo-ku Odori Nishi 1-chome)—and extracts only the municipality portion (“札幌市中央区”).

function extractCityData() {
  // Original address string (e.g., fetched via getValue() from Google Sheets)
  var fullAddress = "北海道札幌市中央区大通西1丁目";
  
  // [STEP 1] Remove the unwanted prefecture name using replace()
  // Strips "北海道" so the string begins cleanly at the city level.
  var addressWithoutPref = fullAddress.replace("北海道", "");
  Logger.log("Prefecture removed: " + addressWithoutPref); 
  // Execution result: 札幌市中央区大通西1丁目
  
  // [STEP 2] Split the string using the ward delimiter "区"
  // Splits at "区" for designated major cities like Sapporo.
  var splitAddress = addressWithoutPref.split("区");
  Logger.log("Split data: " + splitAddress);
  // Execution result: [ '札幌市中央', '大通西1丁目' ]
  // (The delimiter "区" disappears because it was used as the cutting point)
  
  // [STEP 3] Retrieve index 0 of the array and append "区" back to it
  // splitAddress[0] contains '札幌市中央', so we attach '区' to complete the ward name.
  var targetCity = splitAddress[0] + "区";
  Logger.log("Extracted municipality: " + targetCity);
  // Execution result: 札幌市中央区
}

By stripping out unnecessary prefixes with replace() and slicing by specific landmarks with split(), you can accurately extract precisely the information you need from long, messy text blocks!

4. 5 Common Traps for Beginners and How to Solve Them

While string manipulation seems simple at first glance, there are five common pitfalls where beginners often get stuck when things don’t work as expected. Reviewing these now will save you hours of debugging.

Trap 1: replace() Only Replaces the First Match!

In our phone number example (090-1234-5678), using replace("-", "") returned 0901234-5678, leaving the second hyphen untouched.

By default, standard replace() reads the string from left to right and stops immediately after replacing the first match it finds.

[Solution: Use Regular Expressions (RegEx)!]

If you want to replace all occurrences at once, use a regular expression.

Instead of double quotes " ", enclose the target character in forward slashes / / and append g (global flag, which applies the match across the entire string).

var phoneNum = "090-1234-5678";

// ✕ Only the first hyphen is removed
// phoneNum.replace("-", ""); 

// ◯ Using the regular expression /-/g removes all hyphens!
var perfectNum = phoneNum.replace(/-/g, ""); 
Logger.log(perfectNum); // Execution result: 09012345678

*Note: When using regular expressions with / /, do not surround them with quotes.

Trap 2: Getting an Error When Using It on Numbers (Number Type)

As the prefix String. indicates, split() and replace() are methods specifically designed for String objects.

If you pull a pure numeric value (such as 1000) or a Date object using getValue() from a spreadsheet, GAS will throw a TypeError: ... is not a function error.

[Solution: Convert the Number to a String First]

Convert (cast) the data to a string before manipulating it. The easiest way is simply concatenating an empty string "".

var number = 1000;
// var error = number.replace("0", "1"); // ⬅ Throws an error because it's a number!

// Adding an empty string "" converts the number to the string "1000"!
var textNumber = number + ""; 
var success = textNumber.replace(/0/g, "1"); 
Logger.log(success); // Execution result: 1111

Trap 3: Trying to Write the Resulting Array Directly into a Cell

Right after running split(), the returned data is an array like [ '札幌市中央', '大通西1丁目' ]. Passing this directly to setValue() on a single cell can lead to runtime errors or unexpected comma-delimited strings.

[Solution: Specify an Index Number [ ]]

Always specify which index you want using [0] or [1] before assigning it to a variable or writing it to a cell.

// Target only index [0] instead of passing the entire array
var target = splitAddress[0]; 
sheet.getRange("A1").setValue(target); 

Trap 4: Uppercase vs. Lowercase, Full-width vs. Half-width Are Treated Differently

In programming, characters that look almost identical to humans are evaluated as completely distinct entities.

  • "A" and "a" are completely different
  • "1" (full-width) and "1" (half-width) are completely different
  • "Google LLC" and "Google LLC" (with extra spaces) are completely different

If you execute replace("A", "B"), lowercase a will be ignored. If your code isn’t processing text you believe is present, check for case sensitivity, full-width vs. half-width characters, or accidental whitespace.

Trap 5: What Happens When You Split by an Empty String (“”)?

What happens if you provide an empty string "" with no characters as your delimiter?

This turns into a handy trick: it breaks down the string into an array of individual single characters!

var word = "Hello";
var chars = word.split(""); // Passing an empty string
Logger.log(chars);
// Execution result: [ 'H', 'e', 'l', 'l', 'o' ]

Professional engineers use this technique often when analyzing character by character or validating input strings.

5. Summary and Next Steps

In this tutorial, we took a deep dive into two fundamental methods for mastering string manipulation in Google Apps Script:

  • String.split(“delimiter”): Slices a string at designated spots and returns an array.
  • String.replace(“target”, “replacement”): Replaces or deletes specific characters.
  • Use regular expressions (/target/g) when you need to replace all matching characters at once.
  • These are string-only methods; make sure to cast numeric values to strings before using them.

By combining these methods like puzzle pieces, you can automate data cleansing for even the messiest datasets. Be sure to try them out in your own Google Sheets!

Now that you can clean and shape existing data, the next step is automatically inserting fresh incoming data right at the top of your sheets! In Part 18, we will explore Sheet.insertRowBefore() to insert the latest updates directly at the top of a table. This is an essential technique for activity logging and real-time feeds, so stay tuned!