How to use the csv regex column extractor
- Paste a table with a header row. Choose the column by name or number.
- Type a regular expression. In extract mode each capture group, written with parentheses, becomes a new column. In replace mode, type the replacement, using $1 and $2 for groups.
- Tick Ignore case if you need it, then read how many rows matched and how many did not.
- Check the first 100 rows of the result, then download the CSV for the complete table.
Formula
for each row: m = pattern.exec(cell)
extract: new column i = m[i] (blank if the row did not match)
replace: cell = cell.replace(pattern, replacement) for the first match
Patterns use JavaScript regular expression syntax and are tried once per cell. Anchors such as ^ and $ refer to the whole cell.
Contact and order columns
In the sample table the contact column holds text such as Ana Ruiz (ana@example.com). The pattern \(([^@)]+)@([^)]+)\) has two groups, so the result gains two columns holding ana and example.com. Three of the four rows match, and the row that says no contact on file gets blank cells.
On the order column the pattern ^([A-Z]+)-(\d{4})-(\d+)$ has three groups. In extract mode it returns PO, 2026 and 0142. In replace mode with the replacement $2/$3 ($1) it turns PO-2026-0142 into 2026/0142 (PO).
Writing a safe pattern
Start simple and test on a few rows. A group such as ([^@)]+) means one or more characters that are not an at sign or a closing bracket, which is more precise than a dot star. Remember that dot does not match a line break, and that a group that does not take part in a match returns blank.
Patterns that repeat a group that already repeats, such as (a+)+, can take exponential time on some text and freeze a page. The tool refuses that common shape, and skips cells longer than 2,000 characters.
Extract or replace
Extract keeps your original columns and adds the groups next to them, so you can compare. Replace changes only the chosen column and leaves cells that do not match as they were. Both modes use the first match in each cell.
To try a pattern on a single string with highlighting, use the Regex Tester. To pull email addresses and links out of text without a pattern see Extract Emails and URLs from Text.
Assumptions and limits
- The pattern runs in this browser with JavaScript syntax. Other languages differ in lookbehind, named groups and special characters.
- The first row is the header, and tables are limited to 20,000 data rows.
- Cells longer than 2,000 characters are skipped, and the headline says how many.
- Only the first match in a cell is used.
- Nothing typed here leaves your browser.
Frequently asked questions
How do I get the whole match instead of a group?
Wrap the whole pattern in parentheses, so group 1 is the entire match. In replace mode you can also use $& in the replacement for the whole match.
Why is a column blank for some rows?
A row that does not match leaves its new cells blank, and a group inside an optional part of the pattern also returns blank when that part is skipped.
Why was my pattern refused?
The tool refuses a group that already repeats and is repeated again, such as (a+)+ or (\w*)*, because those patterns can run for a very long time. Rewrite it as a single repeat.
Does it change my original table?
No. The result is a new table you can copy or download. The pasted table in the box stays exactly as you typed it.