Extract from a TSV Column with Regex
One column holds a messy string and you need the structured part of it — the order number inside a subject line, the domain out of an email, the numbers in a free-text note. Point a regular expression at that column and every capture group lands in its own new column, aligned to the row it came from. Rows that don't match keep their place with blank cells, so nothing silently shifts out of alignment.
How to use
- Name the column to read — a header name, or a 1-indexed position.
- Write the pattern. Anything the browser's JavaScript engine accepts works, including named groups, lookahead and Unicode escapes. Wrap the part you want in
( ). - Pick a mode. One column per capture group is the default; all matches collects every occurrence into a single joined cell; matched? writes a yes/no flag and is how you audit a pattern before committing to it.
- Name the new columns comma-separated, or leave it blank to get
<column>_1,<column>_2.
Patterns worth stealing
#(\d+) — a ticket or order number after a hash. @([\w.-]+) — the domain from an email address. (\d{4})-(\d{2})-(\d{2}) — an ISO date split into three columns. ([\d.]+)\s*(USD|EUR|GBP) — an amount and its currency. ^(\S+) — the first whitespace-delimited token. (?:https?://)?([^/\s]+) — the host out of a URL, with the scheme made optional and non-capturing so it doesn't become a column.
If the pattern has no capture group at all, the whole match is used as the single new column. That's the quickest way to pull one thing out: \d{4}-\d{2}-\d{2} with no parentheses gives you the date.
What happens to rows that don't match
By default they keep their position and get blank cells. That matters more than it sounds — if non-matching rows were dropped silently, a pattern that is subtly wrong would look like it worked while quietly deleting a third of your data. The status line always reports how many of how many rows matched, so check that number before you trust the output. If it's lower than expected, switch to matched? yes / no, filter for no, and look at what those rows actually contain.
Choose drop the row only when you are deliberately using the pattern as a filter and want the extracted columns at the same time. Rows are matched independently; nothing is reordered, and the original column is left in place alongside the new ones.
FAQ
Which regex flavour is this?
JavaScript's, as implemented by your browser — so \d, \w, \b, lazy quantifiers, lookahead and lookbehind, named groups and \p{…} Unicode property escapes all work. There is no /…/ wrapper: type the pattern only, and use the checkbox instead of an i flag.
Why did my optional group produce a blank column?
Because it didn't participate in that match. An unmatched group is undefined and is written as an empty cell — the column still exists, and the rows where the group did match are populated. That is usually what you want; if not, restructure the pattern so the group is mandatory and let non-matching rows fall out instead.
Can I extract from more than one column at once?
One column per pass. Run it again on the output for the next column — the new columns from the first pass are carried through untouched.
What if the source cell contains a tab?
It can't. A real TSV field cannot hold a tab, so by the time the file is parsed the value is tab-free. If your input has embedded tabs that broke the columns, fix that first with fix ragged rows or escape tabs.
How do I replace rather than extract?
Use find and replace in a column, which does regex substitution in place. This tool only reads — it never modifies the source column.
Privacy
100% client-side. No upload. See the privacy policy.