Extracting Data From Messy Text Columns

You've got a column called "description" and it looks like this:

• Invoice #INV-2024-0456 for client Acme Corp
• Payment received - PO#8834 - $2,450.00
• Ref: TXN-20240315-A | Warehouse pickup
• Order SKU-4421 shipped to NY

There's useful structured data buried in each of those strings - invoice numbers, PO numbers, transaction IDs, SKUs. But it's all mashed into a free-text column. You need each piece in its own column so you can filter, sort, and match on it.

This happens all the time with CRM exports, ERP transaction logs, and anything where humans typed data into a notes field. The information is there, but getting it out requires pattern matching. Here's how to do it in ExploreMyData.

Extract text before or after a delimiter

The simplest extraction. Your value is separated by a consistent character - a dash, a pipe, a colon. Take the string Payment received - PO#8834 - $2,450.00. The PO number is between the first and second dashes.

Click the green + in the Pipeline panel and select Extract Text from the Transform group. Select the description column, choose after_delimiter as the extraction method, and enter - (space-dash-space) as the delimiter. There is no occurrence field to set, and you don't need one: after_delimiter always splits on the first occurrence and hands back everything to the right of it, however many more delimiters are in there. So this row gives you PO#8834 - $2,450.00, second dash and all.

Then run a second Extract Text step with before_delimiter on that result, same delimiter. Now you have PO#8834. Two steps, and the PO number sits in its own column.

The two methods do not generate matching SQL, which is worth knowing when you read the pipeline. before_delimiter is a plain SPLIT_PART("description", ' - ', 1). after_delimiter is not SPLIT_PART at all:

CASE WHEN POSITION(' - ' IN "description") > 0 THEN SUBSTRING("description", POSITION(' - ' IN "description") + LENGTH(' - ')) ELSE '' END

Find the first delimiter, jump past it, take the rest. That is why the result keeps the trailing - $2,450.00 instead of stopping at the next dash. Run after_delimiter with / on a/b/c and you get b/c, not b. When the delimiter is missing from a row entirely, the CASE falls through and you get an empty string.

Extract text between delimiters

Sometimes the value you want is sandwiched between two different markers. The string Invoice #INV-2024-0456 for client Acme Corp has the invoice number between "#" and " for".

Use between_delimiters. Set the start delimiter to # and the end delimiter to for. Result: INV-2024-0456.

This is one of the most useful extraction modes. It works for anything that has consistent start and end markers, even if the text between them varies in length. If either marker is missing from a row, you get an empty string back rather than a partial guess.

Extract with regex for complex patterns

Delimiters work when the structure is consistent. But sometimes the patterns are more fluid. You need "any sequence that looks like INV-YYYY-NNNN" regardless of what surrounds it.

The regex extraction method handles this. Enter a regular expression pattern and ExploreMyData pulls the first match out of each row. One thing to be clear about: this method returns the whole match, not a capture group. Wrapping part of your pattern in parentheses changes nothing about the output here. If you want the piece inside the parentheses, that is a different operation, and it comes up later in this post.

For invoice numbers that follow the INV-YYYY-NNNN pattern:

INV-\d{4}-\d{4}

For SKU codes like SKU-4421:

SKU-\d+

For any dollar amount:

\$[\d,]+\.?\d*

ExploreMyData generates DuckDB's REGEXP_EXTRACT() function:

REGEXP_EXTRACT("description", 'INV-\d{4}-\d{4}', 0) AS "invoice_number"

That trailing 0 is the group index, and Extract Text always passes 0, which means "the entire match".

If a row doesn't match the pattern, the result is an empty string. No errors, no crashed queries. You can filter out empty results afterward if needed.

Position-based extraction

Some codes have fixed positions. If every row starts with a 6-character product code, or the last 4 characters are always a location code, you can extract by position. No expressions needed: the Extract Text dropdown already has three methods for this.

• starts_with takes the first N characters. Set N to 6 for that product code and you get LEFT("description", 6).
• ends_with takes the last N. Set N to 4 for the location code: RIGHT("description", 4).
• position takes a start and a length. Start 3, length 6 pulls characters 3 through 8, or SUBSTRING("description", 3, 6). The start position is 1-based, so start 1 is the first character.

Position-based extraction is fast and reliable when the format is truly fixed-width. It breaks immediately if it isn't, so verify on a few rows before applying.

Checking if a pattern exists

Sometimes you don't need the value at all, only a yes or no. Does this description mention an invoice? Does it contain a dollar amount?

Use Add Column from the Columns group. Name the new column has_invoice and put a CASE expression in the expression box:

CASE WHEN REGEXP_MATCHES("description", 'INV-\d{4}-\d{4}') THEN 'Yes' ELSE 'No' END

Now you can filter to only rows that contain (or don't contain) an invoice number. Handy for triaging mixed-format data, because it separates the rows that follow the expected pattern from the ones that need a human to look at them.

Capture groups: several fields in one step

Real data often hides more than one field in a single column. Take this transaction description:

Ref: TXN-20240315-A | Warehouse pickup

You want three things out of it: the reference ID, the date buried inside that ID, and the fulfillment method. You could chain Extract Text steps (between_delimiters for the ref, after_delimiter for the fulfillment, regex for the digits) and it would work. But this is exactly what capture groups are for, and Extract Text's regex method cannot give you those.

Regex Capture can. It sits in the Transform group, a few entries below Extract Text. You give it one source column and one pattern, then add a row per group: an output column name and the group index you want in it. One step, three new columns.

Pattern: Ref: (TXN-(\d{8})-[A-Z]) \| (.+)

• ref_id, group 1 → TXN-20240315-A
• txn_date, group 2 → 20240315
• fulfillment, group 3 → Warehouse pickup

Each row turns into its own call, so the middle one reads REGEXP_EXTRACT("description", 'Ref: (TXN-(\d{8})-[A-Z]) \| (.+)', 2) AS "txn_date". Group 2 is nested inside group 1 here, which is allowed: groups are numbered by the position of their opening bracket, left to right, so count brackets rather than nesting levels.

That leaves txn_date holding the text "20240315". One Convert Type step, convert to date, makes it a real date column. Leave "Auto-detect date format" on: the compact %Y%m%d form is one of the patterns it tries, so nothing else is needed here.

Two steps, and one messy column has become three clean ones. Both steps stay visible in the pipeline, so when next month's export arrives with a slightly different layout you edit the pattern in place instead of rebuilding a chain of extractions.

When extraction fails on some rows

Not every row will match your pattern. Some descriptions might be blank. Others might use a different format. That's fine. Failed extractions return empty strings or NULLs, not errors.

After extracting, filter where the new column is empty to see which rows didn't match. Often these are edge cases that reveal a second format you didn't know about. Handle those with a second extraction step, or flag them for manual review.

Start extracting from your text data →