One column, two pieces of information
A form sometimes sends Jan Novák into one cell. You want First name and Last name separately — to sort by last name or filter “all the Nováks”. Text to columns (Split text to columns) chops the text by a space, a comma or another separator.
The Sorter prepares data so that filter and chart work.
Splitting survey names
Assume column A on Pruzkum with answers like apple, sweet or names Ema Nová. Practice:
-
On
Poznamkyin A1:A5 write test rows:Anna Svobodová,Ben Novák,Goal 3,apple|5(the last one with a custom separator). -
Select A1:A5. Data → Split text to columns. Separator: Space. Result: A = first name, B = last name (with three words B+C — try just the first two).
-
For
apple|5: select the cell, Split text to columns, separator Custom|. Two columns: answer and number. -
Undo (Ctrl+Z), until you see how it works. On real survey data make a copy of the column — splitting overwrites cells to the right.
-
On the survey: when you have
roll, butterin one cell, split into Answer and Note — or unify by hand torollbefore counting.
Before Split text to columns insert an empty column on the right. Splitting takes B, C… If you have important data there, it overwrites them. A copy of the column = a safety net.
KDYŽ instead of splitting
Sometimes a formula is enough: =LEVÝ(A2;HLEDAT(" ";A2)-1) for the first name — that is more advanced. Split text to columns is fast and does not undo itself — so Undo or a copy.
For survey answers rather unify the text (apple vs Apple) than split — a chart wants one column of answers.
What often goes wrong
Splitting without a free column. You overwrote totals on the right. Ctrl+Z.
A space in the last name. Van der Berg — splits into three columns. Leave complicated names in one column.
A number as text. After splitting 5 stays text — for a chart convert to a number (Format → Number).
🏫 Grade average — do not split
Grades you already have in columns Subject and Grade. You do not need Split text to columns there. When you import grades from an email in one column Maths:2, then split by :.
💰 Pocket money — day and note
Column Mon 50 Kč — split into Day and Amount? Better two columns from the start. Split text to columns only when importing someone else’s data.
📊 Class survey — clean answers
One column = one answer. When the form put more things into one cell, split or simplify to the main category before the summary table.
🏆 Mini dashboard — structured data
The dashboard reads from sheets where columns make sense. Do the splitting on Pruzkum during clean-up, not on the dashboard.
Now you 💪
First: five names on Poznamky — split by space, check column B.
Second: one row with separator |. Undo. On the survey check that every answer is in one cell.
Check: You know when to split and when rather to unify the text. Before splitting a free column or a copy.
What to take from this lesson
- Text to columns = Data → split by space/comma/custom character.
- On the right there must be room — otherwise you overwrite data.
- Survey: one answer = one cell, ideally.
© 2026 Ing. Martin Polak / AlgoRhino · Content usage terms