Test a regex against a real CSV column

Write a pattern and run it against a column of your own file rather than a handful of invented sample strings. Three modes: test for a yes or no column and a match count, extract to pull every capture group into a column of its own, or replace with backreferences. Nothing is uploaded. The file and the pattern both stay in this browser tab.

Not sure what the column holds? Profile it first

Why the sample string is the problem

The usual way a regex goes wrong is not that the pattern is hard. It is that the pattern was written against three strings someone typed into a scratchpad, and the file contains forty thousand strings that were typed by other people over six years. Somebody used a lowercase method name in 2021. Somebody else pasted a value with a trailing tab. A batch of rows came out of an old exporter with a different timestamp format. The pattern is perfect against the sample and wrong against the file, and you find out after the transformation has already run.

So this page starts from the file. You pick a column, write a pattern, and the first thing you get back is a count: how many of the rows scanned actually matched. That single number does most of the work. If you expect 40,000 and see 39,986, you have fourteen rows worth looking at and you know it before anything is rewritten. If you expect 40,000 and see zero, the pattern is wrong in a way that would have been invisible in a scratchpad.

Underneath, the engine is the browser's own. That is deliberate. If you are prototyping a pattern for a Node script or a piece of front end validation, the semantics here are the semantics you will get there, down to the treatment of lookbehind, unicode property escapes and named groups. There is no dialect translation layer to get subtly wrong.

Worked example: turning log lines into columns

Here is access-log-lines.csv, a column of raw log text with one deliberately broken row:

line,entry
1,"2024-05-01T09:14:22Z GET /api/orders?id=4192 200 118ms ip=203.0.113.45"
2,"2024-05-01T09:14:31Z POST /api/checkout 201 402ms ip=198.51.100.7"
3,"2024-05-01T09:15:02Z GET /api/orders?id=4193 404 12ms ip=203.0.113.45"
4,"malformed line with no timestamp at all"

Pick the entry column, switch to extract mode, and use a pattern with four named groups:

(?<method>[A-Z]+) (?<path>\/\S*) (?<status>\d{3}) (?<ms>\d+)ms

What comes back:

line,entry,entry_match,entry_method,entry_path,entry_status,entry_ms
1,...,GET /api/orders?id=4192 200 118ms,GET,/api/orders?id=4192,200,118
2,...,POST /api/checkout 201 402ms,POST,/api/checkout,201,402
3,...,GET /api/orders?id=4193 404 12ms,GET,/api/orders?id=4193,404,12
4,...,,,,,

Extract on entry
3 of 4 rows matched (75.0%)
4 capture groups (method, path, status, ms)
flags: none

Five new columns from one pass. entry_match holds the whole match, and then each named group gets its own column carrying the source column name as a prefix, so status arrives as entry_status. The prefix is not decoration. A file with a path column already in it would otherwise be overwritten, and column collisions in a transformation are the kind of bug that survives three code reviews.

Row four is the interesting one. It did not match, so every extracted column is empty rather than absent, which keeps the file rectangular and makes the failed rows trivially findable with a filter. The summary says three of four, seventy five percent, and that number is the honest headline: a quarter of this file is not what the pattern assumed. On a real log that is your prompt to look at the fourth row rather than ship the pattern.

The 4 capture groups (method, path, status, ms) line is a cheap sanity check that is worth reading every time. Groups are counted from the compiled pattern, not from the matches, so it tells you what the regex declares even on a file where nothing matched. If you meant four groups and it says three, you typed a non-capturing (?: somewhere you did not mean to, and you know that immediately rather than after inspecting output.

Three modes, and the order to use them in

Test. Adds a single column holding yes or no, and reports the match count and percentage. This is where every session should start, because it is the only mode that cannot damage anything. Get the count where you expect it first.
Extract. Adds the whole match plus one column per capture group. Turns semi-structured text into fields. If you write no groups at all, only the whole match is pulled out and the tool tells you so, since a pattern with no parentheses in extract mode is usually a person halfway through writing one.
Replace. Rewrites the chosen column in place. The replacement string takes $1 for a numbered group and $<name> for a named one, so reformatting a date or stripping a prefix is a one-liner. This is the only mode that runs globally.

That last difference catches people, so it is worth stating directly. Test and extract evaluate each cell once, from its start, because the question they answer is whether this row matches and what it yields. Replace runs the pattern over the whole cell repeatedly, because the question it answers is rewrite every occurrence. You do not need to type g for that; replace mode adds it. Typing it yourself in test mode changes nothing, which is the correct outcome and not a bug.

There is a subtlety underneath that mode split which bites people writing their own JavaScript. A global regex object carries a mutable lastIndex, so calling .test() repeatedly on the same object over different strings gives alternating true and false results as the cursor drifts. It is probably the single most common regex bug in JavaScript. Here lastIndex is reset before every row, so each row is judged independently and row 900 is never affected by where the cursor landed on row 899.

Two guards you will be glad of

The empty match check. Before anything runs, the pattern is probed against the empty string. If it matches, you get a warning, because a global replace with a pattern that can match nothing inserts the replacement between every character of every cell. Write \d* meaning digits and you will get your replacement wedged between every letter of every word. The fix is nearly always a star that should have been a plus, or a question mark that should have been {1,}. The warning names the fix rather than just reporting the condition.

The work budget. JavaScript's regex engine backtracks, and certain shapes make it backtrack exponentially. The classic is a nested quantifier such as (a+)+$, and the equally classic real world version is (\s*)* hidden in a pattern meant to trim whitespace. On a long cell that can take longer than the age of the tab you are sitting in, and in a browser it means a frozen page and a lost file. The run has a five second budget. If it is exceeded, the tool stops and tells you which row number it had reached and that nested quantifiers are the usual cause. An error naming a row beats a spinner, and it beats a crash by a much wider margin.

Flags are the third small guard. Only g, i, m, s, u and y are accepted, and anything else is refused by name. That is not gatekeeping: a flag character that gets silently dropped produces a pattern that compiles fine and matches the wrong thing, and you would spend an afternoon looking at the pattern rather than at the one letter after it. An invalid pattern is likewise reported with the browser's own message, cleaned up so the useful part is not buried behind a stack of prefixes.

Patterns that earn their keep on CSV data

Find the padded values. ^\s+|\s+$ in test mode counts the rows with leading or trailing whitespace. It is the first thing to check when a join mysteriously misses a few hundred rows, and the same pattern in replace mode with an empty replacement fixes them.
Split a compound key. ^(?<region>[A-Z]{2})-(?<year>\d{4})-(?<seq>\d+)$ in extract mode turns EU-2024-00381 into three usable columns and flags every row that does not follow the convention by leaving them blank.
Pull a value out of free text. ip=(?<ip>[\d.]+) gets the address out of a log line without you having to know its position. Position-based parsing breaks the first time a field is optional; a pattern does not.
Reformat a date in place. Replace ^(\d{2})\/(\d{2})\/(\d{4})$ with $3-$2-$1 to move a European date into ISO order. Anchor it, or a similar looking substring elsewhere in the cell gets rewritten too.
Count the rows a validation rule would reject. Test mode with the exact pattern from your validator tells you the blast radius before you deploy the rule, which is a conversation with the product owner rather than an incident.
Hunt for a pattern the standard detectors miss. Internal account references, badge numbers and legacy identifiers are all personal data in context and none of them has a standard shape. Find them here, then rewrite them in the anonymizer.

Practical notes

Every row is processed, only twelve are previewed. The preview truncates long values at sixty characters so the table stays readable. The download holds the full file.
A zero match count comes with a diagnosis. Whitespace, case, wrong column: those three cover almost every zero, and the message says so instead of leaving you with a bare number.
Other columns are carried through untouched. Extract and test only append. Replace changes the one column you chose and nothing else.
Name your groups. It costs eight characters and it turns entry_group3 into entry_status in the output file, which is the difference between a CSV somebody can use and a CSV somebody has to decode.
Column name collisions are handled. If a generated name already exists in the file, a numeric suffix is added. Nothing in the original file is ever overwritten by a generated column.
Nothing is uploaded. Parsing, compiling and matching all happen in this tab. Check the network panel if you want to be sure, which is fair enough for a page you are about to paste production log lines into.

Once the pattern is right, the natural next steps are elsewhere on the site. Send the extracted columns to the profiler to see what you actually pulled out, or take the file to the data quality checklist if the reason you were writing a pattern was that something in the export stopped looking right.

Frequently Asked Questions

What is the difference between the three modes?

Test adds one column holding yes or no per row and reports how many of the scanned rows matched. Extract adds a column holding the whole match, then one column for every capture group in the pattern, so a four group pattern gives you five new columns. Replace rewrites the chosen column in place, running the pattern globally, with the replacement string you supply. Test is where you should start: get the match count where you expect it before you let anything rewrite a file.

Which flags can I use?

Six: g, i, m, s, u and y. Anything else is rejected with a message naming the character you typed, rather than being dropped quietly, because a silently ignored i flag looks exactly like a pattern that does not match. You rarely need to type g yourself. Replace mode adds it for you so the replacement applies everywhere in the cell, and test and extract deliberately run without it so each row is judged from the start of its value.

How do named capture groups become columns?

In extract mode each group gets a column named after the source column and the group. Write (?<status>\d{3}) against a column called entry and you get a column called entry_status. Unnamed groups fall back to entry_group1, entry_group2 and so on, in the order the opening parentheses appear. If a name would collide with a column already in the file, a numeric suffix is appended rather than overwriting anything. Naming your groups is worth the extra characters: entry_status and entry_ms are readable six months later in a way that entry_group3 is not.

Why did I get a warning about an empty match?

Because the pattern can match an empty string, and in replace mode a global regex that matches emptiness inserts the replacement between every character of every cell. A pattern like \d* or [a-z]? does this. The tool probes for it before running and warns rather than handing you a mangled file, and the fix is almost always to change a star to a plus, or a question mark to a {1,}, so the pattern has to consume at least one character.

What happens if my pattern is catastrophically slow?

It gets stopped. Nested quantifiers like (a+)+ or (\s*)*$ can send a backtracking engine into exponential time on a single long cell, which in a browser means a frozen tab and a lost file. There is a five second work budget: if the run passes it, the tool aborts and tells you which row number it had reached, and names nested quantifiers as the usual cause. That is more useful than a spinner, and far more useful than a crash.

Why does the preview only show twelve rows?

Because twelve rows of the actual column, side by side with whether each one matched and what came out, is the fastest way to see that a pattern is wrong. The preview table shows the original value, truncated at sixty characters so a long log line does not push everything off screen, then yes or no, then the whole match or the replaced value, then the first capture group. The download is not limited to twelve; every row in the file is processed and written out.

Nothing matched. What is usually wrong?

Three things, in order of frequency. Leading or trailing whitespace, which is invisible in a spreadsheet and fatal to an anchored pattern. Case, because the pattern is case-sensitive unless you add the i flag. And the wrong column, which happens more often than anybody admits when two columns hold similar looking text. The tool says all three when the match count is zero rather than just reporting the zero, because a zero on its own tells you nothing about which of the three it was.

Does the file leave my browser?

No. The CSV is parsed in your tab, the pattern is compiled and run in your tab by the browser's own regex engine, and the result is assembled in your tab. Nothing is uploaded, there is no account, and nothing persists between visits. That matters here because the columns people most want to test a pattern against tend to be the ones full of log lines, order references and email addresses.

Test the pattern on the file, not on three sample strings

Free, no account, no upload. A real match count, named groups as columns, and a guard against the pattern that would have frozen your tab.

Back to the regex tester