Extract Numbers from Text Using REGEX in LibreOffice Calc

Invoice references and exported IDs often mix letters with digits, for example INV-2048. LibreOffice Calc’s REGEX function can extract the digit sequence without forcing you to split the whole cell into columns.

Extract the first group of digits

Put this text in A2:

Invoice INV-2048 paid

Enter in B2:

=REGEX(A2;"[0-9]+")

Expected result: 2048 as text. [0-9] means any ASCII digit; + means one or more in a row. With the optional Replacement argument omitted, Calc returns a matching substring.

Use the result as a number only when appropriate

For a quantity you want to calculate with:

=VALUE(REGEX(A2;"[0-9]+"))

Expected numeric result: 2048. Keep the extracted value as text for identifiers such as INV-0007—converting that to a number drops significant leading zeros.

Prevent a missing match from showing #N/A

If A3 contains No invoice number, the extraction has no digits to find. To show a readable result:

=IFNA(REGEX(A3;"[0-9]+");"No digits found")

Expected result: No digits found. You can also use IFERROR where appropriate, but it catches additional errors beyond the no-match condition.

A common REGEX mistake

Do not add an empty replacement argument when you want to extract:

=REGEX("A123B";"[0-9]+";"")

This removes the matching digits, producing AB. The extraction is:

=REGEX("A123B";"[0-9]+")

Expected result: 123. Replacement and extraction are different modes of the same function.

Limitations and practical cautions

• If a string has several number groups, verify whether you want the first group, all groups, or a specifically positioned one. A broad pattern may extract the wrong digits.
• A decimal such as 12.50 is not one match of [0-9]+; that pattern typically matches 12 first. Design a separate decimal pattern for that task and test your locale.
• If the input contains significant leading zeros, do not wrap the result in VALUE.

Common questions

Does REGEX require special spreadsheet settings? This function has its own expression argument. It should not be confused with COUNTIF’s separate wildcard/regex search settings.

Is regex more suitable than Text to Columns? Use Text to Columns for consistent field separators; use REGEX when you need a text pattern.

Reference: REGEX function guide.

Official reference: LibreOffice Help. This tutorial focuses on LibreOffice Calc; menu names and behavior can vary by version, operating system and locale.