Text extraction often goes wrong because the data is regular in different ways. A comma-separated address, a part number with a fixed prefix and a sentence containing one invoice code should not all be cleaned with the same formula.
Use the structure of the data to choose a method
| Input pattern | Best starting point | Why |
|---|---|---|
Doe, Jane with consistent commas |
Text to Columns or delimiter-based text functions | The separator has a known meaning |
sales@example.com and you need the domain |
TEXTAFTER where supported | A fixed delimiter marks the part you need |
Paid INV-0042 on Tuesday and you need 0042 |
REGEX extraction in Calc | A recognizable pattern exists, not a fixed column position |
| A raw CSV file with quoted commas | A proper CSV import parser | A plain split on every comma would damage quoted fields |
| Text fields in a worksheet with inconsistent punctuation | Review samples first | Automatic splitting can generate wrong columns |
Example: extract a specific code in LibreOffice Calc
If A2 contains the text Paid INV-0042 on Tuesday, the formula:
=REGEX(A2;"INV-[0-9]{4}")
has independently reasoned text result INV-0042. To get only the four digits, use:
=REGEX(A2;"[0-9]{4}")
Expected text result: 0042—the leading zeros must stay intact. The [0-9]{4} pattern means four consecutive ASCII digits and may also match some other four-digit sequence elsewhere in more complex text. Test against representative records before trusting it for every invoice.
LibreOffice’s REGEX documentation explicitly distinguishes extracting a match (replacement argument omitted) from replacing matches. An empty replacement argument removes matching text rather than extracting it.
Example: when a delimiter is simpler
Suppose A3 contains region:West. Where a documented TEXTAFTER function is available, its intended output is West when the delimiter is :. If your target program lacks that function or uses different syntax, a pair of position/text functions—or a split-column command—may be the better choice.
Don’t blindly paste the Calc REGEX formula into Apple Numbers, Zoho Sheet, or Calligra Sheets. Zoho, for example, documents a separate REGEXEXTRACT(text; pattern; ...) function with optional return modes; other programs have their own function names. Zoho’s reference shows the difference.
Preserve source data when cleaning
Make a new output column. Keep the original values visible while you compare results. Check samples that contain repeated separators, empty fields, quoted commas, punctuation and multiple number groups. A formula may be perfectly valid yet extract the wrong number in a particular record.
For CSV files, use the CSV Import Doctor before importing. For known regular delimited data, see Calc Text to Columns; for invoice patterns, see the detailed Calc REGEX example.
Common mistakes
I used a blank replacement argument and lost the code. Omitting the REGEX replacement argument selects extraction mode. Supplying "" requests removal of the match.
I converted 0042 into 42. You have changed an identifier to a number. Keep digit codes as text when the zeros matter.
TEXTAFTER gives me too much text. The delimiter marks the beginning of what follows, not necessarily the end of the desired field. Use a second delimiter or an appropriate extraction pattern.
Verification note: The sample matches were independently checked against the stated patterns and LibreOffice/Zoho vendor documentation. No application execution or application screenshots are claimed.