Columns
Home > Format > Hide & Unhide shows or hides whole columns; Data > Text to Columns (or the live formula =TEXTSPLIT(A2,",") in Excel 365) splits one column into several; and =A2&" "&B2 or =TEXTJOIN(", ",TRUE,A2:D2) joins several columns back into one. Which of the three you need depends on what's wrong with the column, not on memorizing syntax. The fiddly case is unhiding column A itself: a hidden left-edge column has no visible boundary to drag, so select it by name first — type A1 into the Name Box and press Enter — then apply Unhide.
The formula
=TEXTSPLIT(A2, ",") split A2 into columns wherever a comma appears (Excel 365 only)
=SPLIT(A2, ",") the same split in Google Sheets — a different function, same idea
=A2&" "&B2 combine two columns with a space between them
=TEXTJOIN(", ", TRUE, A2:D2) combine a whole row of columns, skipping any blank cellsA worked example
A2 holds the single value "Doe, Jane" — last name, comma, space, first name.
=TEXTSPLIT(A2, ", ")
Two values spill into A2 and B2: "Doe" and "Jane" (Excel 365). In Google Sheets, =SPLIT(A2, ", ") does the same split. Text to Columns (Data tab) does the identical split as a one-time action instead of a live formula — use it if you don't want the split to update automatically.
Which one do I need?
| If you want to… | Use |
|---|---|
| A column (often column A) is hidden and you can't drag to select it | Type its reference into the Name Box (e.g. A1) and press Enter, then Home > Format > Hide & Unhide > Unhide Columns |
| One column holds combined data (name + email, address + city) that needs separating | Data > Text to Columns for a one-time split, or a live formula — TEXTSPLIT(A2, delimiter) in Excel 365, SPLIT(A2, delimiter) in Google Sheets |
| Joining two columns into one (first + last name) | =A2&" "&B2 or =CONCAT(A2,B2) |
| Joining three or more columns, or a whole row, into one | =TEXTJOIN(", ", TRUE, A2:D2) — handles any number of columns and can skip blanks |
Frequently asked questions
How do I unhide column A specifically?
Column A has no left edge to drag from, so the usual right-click-a-boundary trick can't reach it. Click the Name Box (left of the formula bar), type A1, press Enter to select it despite being hidden, then go to Home > Format > Hide & Unhide > Unhide Columns.
What's the difference between Text to Columns and TEXTSPLIT?
Text to Columns runs once and overwrites the column with static values. TEXTSPLIT is a live formula that updates automatically when the source cell changes, but it is Excel-only (365 with dynamic arrays; older perpetual-license Excel does not have it). Google Sheets has no TEXTSPLIT — use SPLIT(text, delimiter) there for the same live-formula result.
Does TEXTJOIN work the same in Google Sheets?
Yes — TEXTJOIN(delimiter, ignore_empty, range) is identical in both. For the reverse operation, Excel uses TEXTSPLIT and Google Sheets uses SPLIT — different function names, same job.
Why did TEXTSPLIT give a #SPILL! error?
TEXTSPLIT writes its results into the cells to the right of the formula. A #SPILL! error means one of those cells already has something in it — clear the row it would spill into and re-enter the formula.
New guides and tools, once a month
DE + EN · double opt-in · no spam