How-to
Text to Columns, on a File Excel Cannot Open
One column holds Smith, John, or SW1A 1AA London United Kingdom, or a timestamp with the
timezone glued to it. You need it as several columns.
The short answer
In DataTray, open Clean & Anonymize, pick the column, and add a split operation — by delimiter, by fixed width, or by pattern. The preview shows the result before anything is written, the output goes to a new file, and it works on files far past Excel's row limit.
Not the same as splitting a file
Worth separating, because the names collide.
Text to columns splits a column into several columns, within the same rows. One address field becomes street, city and postcode.
Splitting a file divides the rows across several files. One big CSV becomes twelve smaller ones.
This page is the first. Splitting a file is here.
Three ways to split a column
By delimiter. Comma, tab, pipe, semicolon, or a character you name. The usual case, and the
one that goes wrong when a value contains the delimiter inside quotes — "Smith, John" should
produce one field, not two, and a splitter that ignores quoting will mangle exactly the rows you
most care about.
By fixed width. By character position. Common in banking and government extracts where every field occupies a defined number of characters, padded with spaces.
By pattern. A regular expression, for when the structure is real but irregular — pulling an order number out of a free-text note, or separating a value from its unit.
Where Excel's version bites
Excel's Text to Columns is genuinely useful, and it has two habits worth knowing about.
It overwrites what is to the right. The split writes into the adjacent columns with no warning. If there was data there, it is gone. The standard defence is to insert blank columns first, every time, and it is easy to forget once.
It re-types the results. Each new column gets Excel's type guess applied, which is where a split postcode loses its leading zero and a split ID becomes scientific notation. The wizard's final step lets you mark each column as text — worth using, and frequently skipped.
Doing the split in a tool that writes a new file avoids the first, and setting the type explicitly avoids the second.
Preview, then commit
The reason to insist on a preview: splitting is one of the operations where a rule that works on the first fifty rows fails on row 12,000. An address field with an extra comma, a name with a suffix, a note somebody typed a pipe into.
The preview shows the resulting columns before anything is written, and any rows the rule could not handle are collected in a rejects file rather than silently truncated.
While you are in there
Splitting a column is almost never the only thing wrong with the file. The same scan that offers the split also reports the trailing whitespace, the mixed date formats, the numbers stored as text and the inconsistent casing — with a count of affected rows for each.
Fixing them in one previewable pass is a minute's work, and the operation stack can be saved as a recipe so next month's export gets the same treatment without you rebuilding it.
Every other free way to do this asks you to upload the file first — and a column full of customer addresses is a poor thing to paste into a web form.
Do this in one pass with — Clean & Anonymize
Trailing whitespace, dates in four formats, numbers stored as text, the leading zeros that vanished, encoding that turned é into é. DataTray finds them without being asked and fixes them in one previewable stack of operations — and in the same pass it finds the personal data, so you can mask, hash or tokenize it before the file goes anywhere. Every finding is a deterministic rule over the column, explainable in one sentence. No model, no API key, nothing uploaded. Learn more about the Clean & Anonymize.
Windows 10/11 (x64) · Free · No account