split text and numbers in google sheets

Split Text & Numbers in Google Sheets

By Kevin HarrisNovember 21, 2025

Are you tired of manually splitting text and numbers in Google Sheets, only to find errors or inconsistencies? You're not alone. But what if there's a way to streamline this process and save time? Introducing the SPLIT TEXT INTO COLUMNS script – your game-changer in Google Sheets.

Understanding the SPLIT TEXT INTO COLUMNS Script

The SPLIT TEXT INTO COLUMNS script is a Google Apps Script that automates the process of splitting text and numbers into separate columns. It's a time-saver that ensures consistency and reduces manual errors.

Google Sheets: Splitting Numbers from Text - Teacher Tech with Alice Keeler

Google Sheets: Split Text to Columns - Teacher Tech with Alice Keeler

Google Sheets: Split Up Student Names - Teacher Tech with Alice Keeler

Script Basics

The script consists of two primary parts: the `onEdit` trigger and the `splitTextIntoColumns` function. The `onEdit` trigger activates the script whenever a user edits a cell in a specific range. The `splitTextIntoColumns` function does the heavy lifting, splitting text into columns based on the pattern you've defined.

Here's a simple breakdown of how the script works:

You define the edit range – the cells that trigger the script.
You specify the split pattern – the character or sequence that divides your data.
The script finds all instances of the split pattern in the edited cell and splits the text into separate columns.

Using the SPLIT TEXT INTO COLUMNS Script

Before we dive into using the script, ensure you've enabled the Google Sheets API and granted the necessary permissions. You can do this via the Resources > Current project's Triggers menu in Google Apps Script editor.

Now, let's use the script. Here's a step-by-step guide:

Access the Google Apps Script editor by clicking on Extensions > Apps Script.
Delete any existing code in the script editor and paste the SPLIT TEXT INTO COLUMNS script.
Modify the script to suit your needs:
- Update the `editRange` variable to match the range of cells you want to trigger the script.
- Define your split pattern in the `splitPattern` variable. This could be a single character (like a comma or a space) or a sequence of characters (like a dash followed by a space).
Test the script by activating the `splitTextIntoColumns` function and running it on a sample cell within your edit range.
Set up a trigger to automate the script. Go to Resources > Current project's Triggers, click on the '+' icon to add a new trigger, and follow the prompts to set up an `onEdit` trigger for your function.

With the script and trigger in place, whenever you edit a cell within the specified range, the script will automatically split the text into columns based on the pattern you've defined.

Advanced Usage: Splitting Text and Numbers

While the SPLIT TEXT INTO COLUMNS script excels at separating text, splitting text and numbers requires a slightly more nuanced approach. Here's how you can achieve this:

Column A	Column B
Name123	456Name
789Name	Name1011

You want to split these texts and numbers into separate columns like this:

Name	Number
Name1	123
456	Name
789	Name
Name	1011

To do this, you can modify the SPLIT TEXT INTO COLUMNS script to use regular expressions (regex) to identify and separate text and numbers. Here's how:

Modifying the SPLIT TEXT INTO COLUMNS Script for Text and Numbers

Update your script like this:

```javascript function splitTextIntoColumns(e) { var r = e.range; var a = r.getValue(); var regex = /\d+/g; // regex for numbers var match; while ((match = regex.exec(a)) != null) { r.offset(0, match.index).setValue(match[0]); regex.lastIndex = match.index; } regex = /\D+/g; // regex for text while ((match = regex.exec(a)) != null) { r.offset(0, match.index).setValue(match[0]); regex.lastIndex = match.index; } } ```

This modified script uses regex to identify and separate numbers (`\d+`) and text (`\D+`) in the cell. It then writes the identified numbers and texts to separate columns.

Once you've modified your script, set up an `onEdit` trigger as before, and you're ready to go. With a simple edit, you'll split your text and numbers into separate columns seamlessly.

Mastering this script opens a world of possibilities, from separating data in customer feedback to managing employee IDs and names.

You might be thinking, "This is great, but what if I need to split data into more than two columns?" The answer? Use multiple `onEdit` triggers and scripts tailored to specific ranges and patterns. For example, you might use one script for three-letter codes and another for two-letter codes in different columns.

Embrace the power of automation, and let the SPLIT TEXT INTO COLUMNS script transform your Google Sheets experience. Happy automating!