Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Extract Numbers and Codes from Text with pandas

Pull order numbers, SKUs, quantities and amounts out of free-text cells like "Re: PO-10442, 3 x SKU AB-1200 @ $12.50". Use str.extract with named groups for one match per cell, extractall for many, fullmatch to validate codes, and a report of the rows where nothing matched.
Data cleaningpandasExcel functions in pandas

Support tickets, bank statement descriptions, order comments and free-text "reference" columns hide structured data inside prose: an order number after "Re:", a product code in the middle of a sentence, a quantity followed by "units", an amount with a currency sign. Excel users pull these out with nested MID, SEARCH and LEN formulas that break the first time the wording changes. In pandas, regular expressions do the job in one line per field, and handle variations in spacing, case and punctuation that would defeat the formula approach. This page, part of Cleaning Excel Data With Pandas, extracts order numbers, product codes, quantities and amounts from a column of ticket notes.

Structured fields pulled out of free-text notes Left: ticket notes such as Re: PO-10442, 3 x SKU AB-1200 @ $12.50, and po 10458 - customer wants 12 units of ab-1203. Right: columns for order number, first product code, quantity and amount, filled where the note contains them: PO-10442, AB-1200, 3, 12.50; PO-10458, AB-1203, 12, blank. A 1 note 2 Re: PO-10442, 3 x SKU AB-1200… 3 po 10458 - … 12 units of ab-1203 4 No order reference given A B C 1 order sku qty 2 PO-10442 AB-1200 3 3 PO-10458 AB-1203 12 4 note extracted fields str.extract

Prerequisites

Bash
pip install pandas openpyxl

tickets.xlsx has six notes: well-formed ones, one in lower case with a space instead of a hyphen, one with two product codes, one with no reference, one with a four-digit order number that looks like a typo, and one with two order numbers and a total.

Step 1 — Extract one field with a named group

str.extract applies a regular expression to every cell and returns the captured groups as columns. Name the group with (?P<name>...) and the column gets that name:

Python
import re
import pandas as pd

tickets = pd.read_excel("tickets.xlsx")
orders = tickets["note"].str.extract(r"(?P<order>PO[-\s]?\d{5})", flags=re.IGNORECASE)
print(pd.concat([tickets["ticket"], orders], axis=1))

The pattern allows a hyphen, a space or nothing between PO and five digits, and re.IGNORECASE catches the lower-case po 10458. Ticket 4, with no reference, gets NaN. Ticket 5's PO-1047 has only four digits and is deliberately not matched — a pattern should be as strict as the real format, so that typos surface instead of being accepted. Ticket 6 has two order numbers; extract returns only the first.

Step 2 — Normalise what you extract

Extracted text inherits the variation of its source: po 10458 and PO-10458 are the same order. Normalise immediately, capturing only the parts that matter and rebuilding a canonical form:

Python
parts = tickets["note"].str.extract(r"PO[-\s]?(?P<num>\d{5})", flags=re.IGNORECASE)
tickets["order"] = "PO-" + parts["num"]
print(tickets[["ticket", "order"]].to_string(index=False))

Capturing just the digits and adding the prefix back means every order number has the same shape, which is what matters when they are later joined against an orders table. Rows without a match stay missing: string concatenation with NaN gives NaN, not "PO-nan".

Step 3 — Extract several fields at once

One pattern with several named groups extracts related fields together. Quantities appear as 3 x or 12 units, and prices as @ $12.50; making each part optional with ? keeps a row from failing just because one field is absent:

Python
sku = tickets["note"].str.extract(r"(?P<sku>\b(?!PO-)[A-Z]{2}-\d{2,4}\b)", flags=re.IGNORECASE)
qty = tickets["note"].str.extract(r"(?P<qty>\d+)\s*(?:x\b|units?\b)", flags=re.IGNORECASE)
price = tickets["note"].str.extract(r"@\s*\$(?P<price>[\d,]+(?:\.\d{2})?)")

fields = pd.concat([tickets[["ticket", "order"]], sku, qty, price], axis=1)
fields["sku"] = fields["sku"].str.upper()
fields["qty"] = pd.to_numeric(fields["qty"]).astype("Int64")
fields["price"] = pd.to_numeric(fields["price"].str.replace(",", "", regex=False))
print(fields.to_string(index=False))

Separate patterns per field are easier to read and debug than one enormous expression, and each can be tested on its own. They can also collide: product codes are two letters, a hyphen and two to four digits, and so is the malformed order number PO-1047 in ticket 5. The first version of this pattern reported it as a product. The negative lookahead (?!PO-) excludes anything starting with the order prefix — a reminder to run every pattern against the other fields' values, not just its own. The word boundaries (\b) in the SKU pattern stop it matching the middle of a longer token. Converting types right after extracting — Int64 for quantities, numbers for prices — turns the text into data you can sum. Notice that ticket 5 writes its quantity as qty 5, with the number after the word, which the pattern does not cover. Whether to add that form as an alternative — (?:qty\s*(?P<qty2>\d+)) — depends on how often a source uses it; the point is that the gap is visible in the output rather than hidden.

Anatomy of the SKU pattern The regular expression for product codes, with callouts. The named group gives the output column its name. The word boundaries stop matches inside longer tokens. Two letters, a hyphen and two to four digits describe the real code format. The IGNORECASE flag accepts lower-case input, which is normalised afterwards with str.upper. 1 1 pattern = (r"(?P<sku>" # named group -> column "sku" 2 2 r"\b(?!PO-)[A-Z]{2}-\d{2,4}\b)") 3 3 sku = notes.str.extract(pattern, flags=re.IGNORECASE) 4 4 sku["sku"] = sku["sku"].str.upper() 1 (?P<sku>…) names the output column 2 \b … \b whole tokens; (?!PO-) skips orders 3 IGNORECASE: accept ab-1203 4 normalise to AB-1203

Step 4 — Extract every match with extractall

Ticket 3 mentions two products and ticket 6 two orders. str.extractall returns every match, with an extra index level, match, counting the matches within each cell:

Python
all_skus = tickets["note"].str.extractall(r"(?P<sku>\b(?!PO-)[A-Z]{2}-\d{2,4}\b)", flags=re.IGNORECASE)
all_skus["sku"] = all_skus["sku"].str.upper()
print(all_skus)

lines = all_skus.reset_index(level="match").join(tickets["ticket"])
print(lines[["ticket", "match", "sku"]].to_string(index=False))

The result has one row per match, which is the right shape for a "ticket lines" table: ticket 3 contributes two rows, tickets without a code contribute none. Joining back on the original index attaches the ticket number. When you only need the matches as a list per cell, str.findall returns Python lists instead, which suits a quick explode or a count of matches per row.

Which of the three methods to reach for depends on how many values a cell can hold and what shape you want back:

extract, extractall and findall compared A comparison of three pandas string methods. str.extract returns the first match per cell as columns, one output row per input row. str.extractall returns every match as rows with an extra match index. str.findall returns every match as a Python list per cell. All three take flags such as IGNORECASE. Matches per cell Returns Best for str.extract first only DataFrame, one row per cell one field per record str.extractall all rows with a match index line tables (ticket → SKUs) str.findall all a list per cell counts and quick checks

Step 5 — Validate codes that are supposed to be codes

Extraction finds codes inside text. When a column should be a code — a reference column that sometimes holds rubbish — check it with str.fullmatch, which requires the whole value to match:

Python
refs = pd.Series(["PO-10442", "PO-1047", "po-10458", "PO-10460 ", "see email"])
valid = refs.str.strip().str.upper().str.fullmatch(r"PO-\d{5}")
print(pd.DataFrame({"ref": refs, "valid": valid}))

fullmatch is stricter than contains or extract: PO-1047 fails for having four digits and see email fails outright, while stripping and upper-casing first lets harmless variations through. Use it to separate clean values from the ones that need a person, as in Validate Excel Columns Before Import with pandas.

Step 6 — Report the rows where nothing matched

Every extraction should end with a look at what it missed. Rows without a match are either genuinely missing the field or evidence that the pattern is wrong:

Python
missing = fields[fields["order"].isna()].merge(tickets[["ticket", "note"]], on="ticket")
print(missing[["ticket", "note"]].to_string(index=False))

suspicious = tickets["note"].str.contains(r"\bPO\b", case=False) & fields["order"].isna()
print("mentions PO but no valid order:", tickets.loc[suspicious, "ticket"].tolist())

Ticket 4 genuinely has no reference. Ticket 5 mentions PO but its number is malformed, which is exactly the case a person should see — the second check catches it by looking for the prefix without the full pattern. Writing both lists to a "Needs review" sheet next to the cleaned data closes the loop. Highlight Cells Containing Specific Text with openpyxl shows how to mark such rows in the workbook itself.

Writing the results to Excel

Put the original notes, the extracted fields and the review list in one workbook, so the cleaning can be checked against its source:

Python
with pd.ExcelWriter("tickets_extracted.xlsx") as writer:
    fields.merge(tickets[["ticket", "note"]], on="ticket").to_excel(writer, sheet_name="Tickets", index=False)
    lines[["ticket", "sku"]].to_excel(writer, sheet_name="Ticket lines", index=False)
    missing[["ticket", "note"]].to_excel(writer, sheet_name="Needs review", index=False)
print(pd.ExcelFile("tickets_extracted.xlsx").sheet_names)

Common pitfalls

  • Patterns looser than the format. \d+ after PO accepts typos; state the real length.
  • Forgetting case. Add re.IGNORECASE and normalise the result.
  • One giant pattern. Separate patterns per field are easier to test and fix.
  • Using extract when a cell can hold several values. Use extractall or findall.
  • Leaving extracted numbers as text. Convert with to_numeric straight away.

Performance notes

str.extract compiles the pattern once and runs it through the regular expression engine for each cell, handling hundreds of thousands of rows per second for simple patterns. Avoid patterns with nested repetition such as (\w+\s?)+, which can take exponential time on long cells; anchored, specific patterns are both faster and more correct.

Conclusion

Use str.extract with named groups for one value per cell, normalise the captured text into a canonical form immediately, and use separate, specific patterns for each field. Switch to str.extractall when a cell can hold several values, validate code columns with str.fullmatch, convert extracted numbers to real types, and always list the rows where nothing matched — they tell you whether the data or the pattern needs fixing.

Frequently asked questions

What is the difference between str.extract and str.extractall? str.extract returns the first match in each cell as columns, one row per original row. str.extractall returns every match, with an extra index level numbering the matches within each cell, so one row of input can become several rows of output.

How do I name the extracted columns? Use named groups in the pattern, written (?P...). str.extract turns each named group into a column with that name, which is clearer than numbered columns 0, 1, 2.

Why do I get NaN for some rows? Those cells contain no text matching the pattern. That is useful information: list those rows and check whether the pattern is too strict or the cells genuinely lack the value.

How do I match regardless of case? Pass flags=re.IGNORECASE to str.extract, or start the pattern with (?i). Then normalise the extracted value, for example with str.upper(), so "po 10458" and "PO-10458" end up identical.

More on data cleaning

More on data cleaning →