How to Split Cells into Rows in Google Sheets & Excel

The Hidden Cost of Overstuffed Cells

Every agency owner, marketer, or operator has met the monster cell: a single box crammed with “John Smith, Maria Clara” or “SKU-1 / SKU-2”. It looks tidy, but it quietly breaks your reporting. You can’t filter by a single person, you can’t count products accurately, and your CRM exports become a mess.

The fix is simple in theory: split that one cell into two rows. The real question is how to do it reliably, especially when you have hundreds or thousands of records. Let’s walk through the best options—from quick manual tricks to fully automated AI agents.

1. Manual Split in Excel: Cut, Insert, Paste

Use this when you only have a handful of cells to fix.

Steps:

1. Click the cell with combined data, e.g., A2 containing "John Smith, Maria Clara".
2. Double‑click the cell or use the formula bar to place your cursor inside.
3. Highlight the part you want on the new row (e.g., "Maria Clara") and press Ctrl + X to cut.
4. Right‑click the row number below (row 3) and choose Insert > Insert Row Above.
5. Click the corresponding cell in the new row (A3) and press Ctrl + V to paste.

Pros:

• Intuitive, no formulas.
• Perfect for small, one‑off fixes.

Cons:

• Painful and slow for large datasets.
• Easy to mis‑click and break alignment.

2. Formula‑Based Split in Excel

If your delimiter is consistent (comma, slash, semicolon), formulas can split values into separate rows.

Example: A2 contains John Smith, Maria Clara.

• In A3 (first row): =TRIM(LEFT(A2, FIND(",", A2) - 1))
• In A4 (second row): =TRIM(MID(A2, FIND(",", A2) + 1, LEN(A2)))

Copy these formulas down for each record, then optionally paste values to freeze the result.

Pros:

• Reusable without coding.
• Good for structured, predictable text.

Cons:

• Breaks if the delimiter pattern changes.
• Still requires manual copying and cleanup.

3. Power Query: Split Into Rows at Scale (Excel)

Power Query is ideal when you have large, recurring datasets.

Steps:

1. Select your table or range, including the column to split.
2. Go to Data > From Table/Range to open Power Query.
3. In the Query Editor, select the combined column.
4. Choose Transform > Split Column > By Delimiter.
5. Pick your delimiter (e.g., comma) and open Advanced options.
6. Select Split into Rows instead of columns.
7. Click OK, then Close & Load to send the cleaned data back to Excel.

Pros:

• Very scalable and refreshable.
• Keeps a live connection to your source.

Cons:

• More clicks and a learning curve.
• Harder for non‑technical teammates.

4. Google Sheets: TEXTSPLIT and TRANSPOSE

In Google Sheets, you can mix built‑in functions to simulate “split into rows”.

Steps (comma‑separated values in A2):

1. In a blank area, enter: =TRANSPOSE(SPLIT(A2, ","))
2. This splits the cell by comma and flips the results vertically into rows.
3. Drag the formula down or wrap it in an array formula to handle more cells.

Pros:

• Native, no add‑ons.
• Great for moderate datasets.

Cons:

• Still formula‑heavy.
• Easily broken if someone edits formulas.

5. VBA & Apps Script: Macro Automation

For power users, VBA (Excel) or Apps Script (Google Sheets) can loop through every selected cell, split by a delimiter, and insert new rows.

High‑level pattern:

• Loop through each cell in a range.
• Use Split() by your delimiter.
• For each piece, insert a row and place the value.

Pros:

• Fully automated once written.
• Extremely flexible.

Cons:

• Requires coding skills and maintenance.
• Scripts can break with layout changes or new data formats.

6. Let an AI Computer Agent Do the Clicking

All the methods above assume you’re the one driving. But what if you could show an AI computer agent the workflow once and let it repeat it across any spreadsheet?

With a computer‑use agent like Simular, you can:

• Open Excel or Google Sheets.
• Demonstrate how you split a few sample cells into two rows using your preferred method (Power Query, formulas, or simple cut‑and‑insert).
• Save that as a reusable workflow.

The agent can then:

• Detect columns with overpacked cells.
• Apply your chosen split logic.
• Insert rows, paste values, and even update downstream tools like your CRM or reporting sheet.

Pros:

• Works across both Google Sheets and Excel.
• Handles thousands of rows without fatigue.
• Transparent execution: you can see, inspect, and adjust every step.

Cons:

• Requires an initial “teaching” pass.
• Best suited when you commit to ongoing automation, not one‑time fixes.

For busy teams, that’s the real win: you stop being the bottleneck for spreadsheet cleanup and promote yourself back to strategist, while the agent quietly does the cell surgery in the background.