How to Use TEXTSPLIT in Google Sheets and Excel – Practical Guide

1. Manual and Traditional Ways to Split Text

Before you automate, it helps to master the basics. These methods are perfect for smaller jobs or documenting the exact logic you’ll later hand to an AI agent.

‍

1.1 Excel: Text to Columns wizard

1. Open your file in Excel.
2. Select the column with combined text (for example, full address or "Name, Email").
3. Go to Data → Text to Columns.
4. Choose Delimited and click Next.
5. Tick the delimiter(s) you use (comma, space, semicolon, etc.).
6. Preview the split in the window; adjust delimiters if needed.
7. Choose the destination (overwrite the original column or a new range).
8. Click Finish. This is fast for one-off jobs but not reusable—every new file means repeating the clicks.

Official doc: https://support.microsoft.com/en-us/office/split-text-into-different-columns-with-the-convert-text-to-columns-wizard-30b14928-5550-41f5-97ca-7a3e9c363ed7

‍

1.2 Excel: TEXTSPLIT formula Use TEXTSPLIT when you want a reusable, formula-driven split:

• Basic column split:
  • In B2, enter: =TEXTSPLIT(A2,",")
  • This splits the value in A2 into columns wherever a comma appears.
• Split into rows:
  • =TEXTSPLIT(A2,,";") splits on semicolons down rows.
• Multiple delimiters:
  • =TEXTSPLIT(A2,{",",";"}) splits on both commas and semicolons.
• Ignore empty values:
  • =TEXTSPLIT(A2,",",,TRUE) skips blanks caused by consecutive delimiters.

Official doc: https://support.microsoft.com/en-us/office/textsplit-function-b1ca414e-4c21-4ca0-b1b7-bdecace8a6e7

‍

1.3 Excel: Legacy formulas (LEFT, RIGHT, MID, SEARCH) Sometimes you only need a single slice:

• Extract first name from "First Last" in A2:
  • =LEFT(A2,SEARCH(" ",A2)-1)
• Extract domain from email in A2:
  • =RIGHT(A2,LEN(A2)-SEARCH("@",A2)) These are useful for very specific cuts but quickly become hard to maintain compared to TEXTSPLIT.

‍

1.4 Google Sheets: Split text to columns (menu)

1. Open your sheet in Google Sheets.
2. Select the column with combined data.
3. Click Data → Split text to columns.
4. In the separator dropdown, choose Comma, Space, Custom, etc.
5. Review the preview and adjust as needed.

Official doc: https://support.google.com/docs/answer/54813

‍

1.5 Google Sheets: SPLIT function Sheets doesn’t have TEXTSPLIT yet, but SPLIT covers most use cases:

• Put this in B2: =SPLIT(A2,",",TRUE,TRUE)
  • The third parameter (TRUE) treats consecutive delimiters as one.
  • The fourth parameter (TRUE) trims whitespace.
• Split into rows by combining with TRANSPOSE:
  • =TRANSPOSE(SPLIT(A2,";"))

Official SPLIT doc: https://support.google.com/docs/answer/3094136

These manual methods are your blueprint. The patterns you discover here become the rules you’ll encode into no-code flows and your AI agent.

‍

2. No‑Code Automation with Popular Tools

Once you repeat the same splits on every export—weekly CRM lists, daily Shopify orders, ad platform dumps—it’s time to automate with no-code platforms.

‍

2.1 Zapier: Auto-clean new rows in Sheets Use Zapier when your data source already connects (HubSpot, Typeform, Airtable, etc.).

1. Trigger: "New row in Google Sheets" where raw data lands.
2. Action: "Update row in Google Sheets".
3. In the action, use Zapier’s built-in Formatter or write formulas directly in your destination columns, for example:
   • Column B formula: =SPLIT(A2,",",TRUE,TRUE)
4. Zapier keeps inserting new raw rows; your formulas propagate down, keeping the dataset clean.

Pros: Easy, works well with Sheets, great for marketers. Cons: Formula logic still lives in Sheets; Zapier just keeps the pipeline running.

‍

2.2 Make (Integromat): Advanced branching and batching Make is ideal when you need more logic:

1. Module 1: Watch a Google Drive folder for new CSVs.
2. Module 2: Import into Google Sheets.
3. Module 3: Run a "Google Sheets" module to append formulas using SPLIT or create helper columns.
4. Module 4: Push cleaned data to your warehouse, CRM, or ads tool.

Pros: Flexible, great for multi-app workflows. Cons: Visual scenarios get complex; still no native awareness of advanced Excel TEXTSPLIT.

‍

2.3 Power Automate: For Microsoft 365 shops If your team is in the Microsoft ecosystem:

1. Trigger: "When a file is created in SharePoint/OneDrive".
2. Action: Use Excel Online (Business) connectors to add a table.
3. Add columns whose formulas use TEXTSPLIT, for example:
   • =TEXTSPLIT([@[FullName]]," ") to break first and last names.
4. Save the workbook or export as CSV for downstream tools.

Pros: Deep Excel integration, good for corporate IT. Cons: Formula authoring still manual; you manage every edge case yourself.

‍

3. AI Agent–Driven TEXTSPLIT at Scale

Manual formulas and no-code flows help, but as data sources multiply—multiple CRMs, ad accounts, marketplaces—they become a maintenance tax. This is where a Simular AI computer agent changes the story.

Simular Pro agents can use your desktop like a power user: opening Excel workbooks, navigating Google Sheets in the browser, editing formulas, and moving files with production-grade reliability.

‍

3.1 Agent that cleans every new Excel export Imagine your sales ops manager dropping raw CRM exports into a "To Clean" folder.

1. A Simular agent is triggered when a file appears.
2. It opens the workbook in Excel.
3. It inserts helper columns with your chosen TEXTSPLIT formulas, such as:
   • =TEXTSPLIT(A2,",",,TRUE) for splitting comma-separated fields while ignoring blanks.
   • =TEXTSPLIT(B2,"=",",") to create a 2D array of "label=value" pairs.
4. It autofills formulas down, converts results to values, and moves the cleaned file into a "Ready for BI" folder or uploads it to your reporting system.

Pros:

• No need to build or maintain complex flows; you describe the steps once.
• Handles multi-step workflows with thousands of actions.
• Transparent execution—every click and formula is visible and adjustable.

Cons:

• Initial onboarding effort to show the agent your exact process.

‍

3.2 Agent that standardizes Google Sheets inputs for marketing For agencies juggling dozens of clients, data formats change constantly.

1. A Simular agent opens a shared Google Sheet where each client’s raw leads land.
2. It applies SPLIT-based formulas, for example splitting "Full Name" or parsing UTM parameters from a single URL column.
3. It normalizes headers, ensures consistent column order, and flags rows where SPLIT failed (missing delimiter, malformed URL).
4. Finally, it copies the clean tab into a master reporting sheet or exports to Excel for finance.

Pros:

• Works across browser tabs, logins, and extensions.
• Adapts when a client adds an extra field instead of breaking like rigid scripts.

Cons:

• Requires clear naming conventions so the agent can reliably find the right sheet and range.

‍

3.3 Agent orchestrating end-to-end list prep For bigger teams, you can plug Simular into your production pipeline using webhooks.

1. A webhook from your CRM or warehouse signals "New list ready".
2. Simular Pro wakes the agent.
3. The agent downloads the file, runs Excel TEXTSPLIT or Sheets SPLIT cleaning, validates row counts and key columns, then uploads the result to your email platform or ads manager.

Compared to no-code tools, Simular’s AI agent behaves like a tireless operations assistant. You show it the workflow once; after that, delegating text splitting in Excel and Google Sheets becomes a decision, not a task.