A common problem when importing CSV files or copying data from external software into Google Sheets is that multiple pieces of information are crammed into a single cell. For example, a cell might contain “Smith, John” or “[email protected]”.
To sort, filter, or analyze this data properly, you must separate it into distinct columns (e.g., Column A for First Name, Column B for Last Name). While you can use the “Data > Split text to columns” menu tool, that process is manual and static. If the raw data updates tomorrow, you have to perform the manual split all over again.
To automate this process dynamically, you must use the SPLIT function. In this guide, you will learn how to use the SPLIT function to automatically separate text strings.
The Basic SPLIT Syntax
The SPLIT function requires two primary arguments:
=SPLIT(text, delimiter)- text: The cell containing the messy string you want to break apart.
- delimiter: The specific character (like a comma, space, or hyphen) that acts as the dividing line. This must be wrapped in quotation marks.
Use Case 1: Splitting Names by a Comma
Assume cell A2 contains the text: Smith, John. You want “Smith” in column B and “John” in column C.
Click into cell B2 and type:
=SPLIT(A2, ",")Google Sheets will look at the text, find the comma, chop the text in half at that exact location, and spill the results into the adjacent cells. Note: If your raw data includes a space after the comma (e.g., “Smith, John”), the resulting “John” will have a leading space. To fix this, use comma-space as your delimiter: =SPLIT(A2, ", ").
Use Case 2: Extracting Usernames from Emails
Suppose you have a list of corporate emails (e.g., [email protected]) and you want to extract just the username for a directory.
The delimiter in this case is the “@” symbol.
=SPLIT(A2, "@")This will drop “j.smith” into one cell, and “company.com” into the next cell. The delimiter itself (the @ symbol) is permanently deleted in the process.
Advanced Usage: Multiple Delimiters
The SPLIT function has a third, optional argument that dictates how it handles multiple different delimiters in the same string.
=SPLIT(text, delimiter, [split_by_each])Suppose cell A2 contains a messy product code: AB12-CD34/EF56. You want to split this string every time a hyphen OR a forward slash appears.
=SPLIT(A2, "-/")By default, the split_by_each argument is set to TRUE. This means Google Sheets treats every single character inside your quotation marks as an independent trigger. It will chop the text when it sees a hyphen, and it will chop it again when it sees a slash, resulting in three perfectly clean columns: AB12, CD34, and EF56.
If you only wanted to split the text if the exact sequence “-/” appeared together, you would set the third argument to FALSE: =SPLIT(A2, "-/", FALSE).
By mastering the SPLIT function, you can build automated pipelines that instantly sanitize and organize raw text imports the moment they hit your spreadsheet.