If you've ever pasted a wall of comma-separated text into Google Sheets and watched it land in a single cell, you already know the frustration. Delimiters are the invisible rules that tell Sheets where one value ends and the next begins. Get them right, and your data snaps into place perfectly.
What Is a Delimiter?
A delimiter is a character used to separate pieces of data. Commas, tabs, pipes, and semicolons are the most common ones. When you import a CSV file or split a text string, the delimiter is what tells the program how to cut the data apart.
Google Sheets handles delimiters in a few different places, and each one works slightly differently. Knowing which tool to use saves you a lot of manual cleanup.
Importing Data with the Right Delimiter
When you import a file using File > Import, Sheets gives you a dialog box with a "Separator type" option. This is where you choose your delimiter before the data loads.
- Go to File > Import in Google Sheets.
- Upload your file or choose one from Drive.
- In the import dialog, set the "Separator type" to match your file (comma, tab, semicolon, or custom).
- Choose whether to replace the current sheet or create a new one.
- Click Import data.
If your file uses a pipe character as a separator, choose "Custom" and type the pipe symbol into the field. Sheets will split every row on that character.
Tip: If you're not sure what delimiter your file uses, open it in a plain text editor first. You'll spot the separator character between every value on each line.
Using the SPLIT Function
The SPLIT function is one of the most useful tools in Sheets for working with delimited text. It breaks a string into separate cells based on whatever separator you specify.
The basic syntax is: =SPLIT(text, delimiter). For example, =SPLIT(A1, ",") will split a comma-separated string in cell A1 across multiple columns.
| Function Example | Input | Output |
|---|---|---|
| =SPLIT(A1, ",") | apple,banana,cherry | apple | banana | cherry |
| =SPLIT(A1, "|") | red|green|blue | red | green | blue |
| =SPLIT(A1, ";") | one;two;three | one | two | three |
| =SPLIT(A1, " ") | hello world | hello | world |
SPLIT also has an optional third argument. Set it to FALSE if you want Sheets to treat each character in the delimiter string as a single unit rather than splitting on each character individually.
Splitting Text with "Split Text to Columns"
If you already have data inside a sheet and just need to separate it, use the built-in Split text to columns feature. Select your column, then go to Data > Split text to columns. A small toolbar appears at the bottom of your selection where you pick the separator.
- Works directly on existing cell data without a formula.
- Supports comma, semicolon, period, space, or a custom character.
- Overwrites data in adjacent columns, so make sure those are empty first.
- Can't be undone cleanly once the data spreads, so duplicate your data before using it.
Changing Delimiters Before Importing
Sometimes your file uses the wrong delimiter for what Sheets expects. The fastest fix is to convert the delimiter before you import. If you have a pipe-separated file but need commas, a tool like the online delimiter converter lets you swap delimiters in seconds without opening a text editor.
This is especially handy when you're dealing with files exported from databases or legacy systems that default to unusual separators.
Key Points
- A delimiter is a character that separates values in text data, such as a comma, tab, pipe, or semicolon.
- Use File > Import to set the correct delimiter when bringing external files into Google Sheets.
- The SPLIT function splits text strings into separate cells using any delimiter you specify.
- "Split text to columns" under the Data menu handles existing in-sheet data without a formula.
- If your file uses the wrong delimiter, convert it first using an online delimiter converter before importing.
Get Your Data in Shape
Delimiters are a small detail that causes a surprising amount of friction. Once you know where Google Sheets expects them and which tool to use in each situation, importing and splitting data becomes quick and reliable.
If you run into a file with a stubborn or unusual separator, prep it with a free delimiter converter before it ever hits your spreadsheet. A little prep upfront saves a lot of reformatting later.