When you are working in Google Sheets, many times you get data that is combined in one cell — like “FirstName LastName”, “City, State”, or “TicketID-Date”. Handling this kind of data can be a headache if you don’t know the right function.
Instead of manually copying, pasting, or using “Text-to-Columns” again and again, Google Sheets gives us a super handy function: SPLIT().
This function automatically splits text into multiple columns or rows based on a character you choose (called a delimiter). For example, you can split names by a space, or split addresses by a comma, or split product codes by a hyphen. And the best part — it is dynamic. If new data comes in, SPLIT will handle it automatically.
In Excel, there is a similar function called TEXTSPLIT(), but it is available only in the latest Office 365. Earlier Excel users rely on the manual “Text-to-Columns” tool, which is not dynamic. This is one big advantage of Google Sheets: everyone gets SPLIT by default.
Syntax of SPLIT Function in Google Sheets
=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])
- text → The text or cell reference you want to split.
- delimiter → The character(s) you want to split by (like space
" ", comma",", dash"-"). - split_by_each (optional) → Default is TRUE. If TRUE, every character in the delimiter is considered separately.
- remove_empty_text (optional) → Default is TRUE. If TRUE, empty results are removed.
Real-Life Examples of SPLIT Function
Example 1: Split Full Name into First and Last Name
Suppose you have Ravi Kumar in cell A2.
=SPLIT(A2," ")
👉 Result: Ravi | Kumar
Example 2: Split Address into City and State
If A2 has Raipur, Chhattisgarh
=SPLIT(A2,", ")
👉 Result: Raipur | Chhattisgarh
Example 3: Split Helpdesk Ticket ID
Your ticket column has values like TCK-2025-001.
=SPLIT(A2,"-")
👉 Result: TCK | 2025 | 001
Example 4: Split Email ID into Username and Domain
If A2 has support@smarttejas.com
=SPLIT(A2,"@")
👉 Result: support | smarttejas.com
Example 5: Split Product List into Multiple Columns
If A2 has Laptop, Mouse, Keyboard, Monitor
=SPLIT(A2,", ")
👉 Result: Laptop | Mouse | Keyboard | Monitor
Example 6: Split Text into Each Character
If A2 has DATA and you use:
=SPLIT(A2,"")
👉 Result: D | A | T | A
Example 7: Handling Extra Spaces (Remove Empty)
If A2 has Apple,,Mango,,Banana
=SPLIT(A2,",")
👉 By default, empty cells are skipped → Apple | Mango | Banana.
If you set remove_empty_text to FALSE:
=SPLIT(A2,",",TRUE,FALSE)
👉 Result: Apple | | Mango | | Banana
Example 8: SPLIT with ARRAYFORMULA
If you want to split multiple rows automatically:
=ARRAYFORMULA(SPLIT(A2:A10," "))
👉 Splits all names in column A into two columns (first name and last name).
Example 9: SPLIT + TRANSPOSE
To split into rows instead of columns:
=TRANSPOSE(SPLIT(A2,","))
👉 Converts a comma-separated list into a vertical list.
Example 10: Real MIS Example – Split HR Employee Codes
Suppose employee codes look like HR-EMP-0456.
=SPLIT(A2,"-")
👉 Result: HR | EMP | 0456
This helps HR or MIS officers quickly separate department, role, and employee ID.
SPLIT vs TEXTSPLIT (Google Sheets vs Excel)
- Google Sheets SPLIT:
- Available to all users.
- Simple syntax.
- Always dynamic.
- Excel TEXTSPLIT:
- Works only in latest Office 365.
- Syntax:
=TEXTSPLIT(text, col_delimiter, row_delimiter, …) - More powerful because you can define both column and row delimiters at the same time.
👉 For most MIS and reporting needs, Google Sheets’ SPLIT is more than enough. If you need advanced row+column splitting, Excel’s TEXTSPLIT may be better.
Limitations of SPLIT in Google Sheets
- It can only split based on delimiters, not advanced patterns (like multiple spaces or mixed text/number).
- Results expand into adjacent cells, so make sure nothing blocks the cells to the right.
- Doesn’t directly support multiple delimiters at once (workaround: use REGEXREPLACE before splitting).
Conclusion
The SPLIT function in Google Sheets is a lifesaver for anyone handling combined text values. Whether you are separating employee names, cleaning up helpdesk tickets, breaking down product codes, or formatting addresses, SPLIT makes your data clean, structured, and ready for analysis.
While Excel users still struggle with manual “Text-to-Columns” or need the latest TEXTSPLIT function, Google Sheets users can enjoy SPLIT’s simplicity and dynamic power anytime.
👉 If you combine SPLIT with ARRAYFORMULA, QUERY, UNIQUE, and FILTER, you can build professional MIS reports without touching the mouse again and again.