Extract the last number from a string

Extract the last number from a string

To extract the last number from a string, you can use a formula based on the TEXT and REGEXEXTRACT functions with a specific pattern.

How to extract numbers from the end of a text string?

Here are the steps to extract the last numeric value from the end of a string:

  1. Select cell D3.
  2. Type =VALUE(REGEXEXTRACT(B3, “\d+(?!.*\d)”))
  3. Press Enter.
  4. The formula extracts only the last numeric sequence in the string.

Example

In the example, we have a dataset in the range B3:B7 containing text strings with embedded numerical values. The goal is to extract the last numeric value from each string and display it in column D.

Formula:

=VALUE(REGEXEXTRACT(B3, “\d+(?!.*\d)”))

Extract the last number from a string

The formula in cell D3 uses the REGEXEXTRACT function to identify the final numeric sequence in each text entry and converts it to a number using the VALUE function.

Explanation

Evaluate the formula from the inside out:

REGEXEXTRACT(B3, “\d+(?!.*\d)”): REGEXEXTRACT extracts text from a string in cell B3 that matches a given regular expression.

Here, the (“\d+(?!.*\d)”) pattern processes the string. The “\d+” pattern matches one or more digits (\d represents digits 0-9, and + means “one or more”) and identifies numeric sequences in the string.

A “negative lookahead” pattern (?!.\d) ensures the matched digit sequence is the last one in the string. After that, “.” matches any characters after the digit, and (?!.*\d) ensures that no more digits exist after the current match. This pattern means, “Do not allow any digits to follow this match”.

VALUE(…) converts text to a numeric value and ensures the extracted number is treated as a true number (not text) for calculations or formatting. The output of REGEXEXTRACT is text, even if it contains numeric characters.

Using the TEXT function instead of VALUE

You can use another formula to extract the last number from the end of a text string:

=TEXT(REGEXEXTRACT(B3, “\d+(?!.*\d)”),”0″)

Using the TEXT function instead of VALUE

TEXT(…, “0”) converts the extracted numeric value into a specified number format. In this case, the “0” format ensures the value is displayed as a number without additional formatting. The output of REGEXEXTRACT is text, even if it contains numbers. TEXT converts this text into a numeric format, making it suitable for calculations or consistent formatting. For example, if the extracted value is “456”, the TEXT function formats it as 456.

Workaround with TEXTSPLIT and TEXTJOIN

If REGEXEXTRACT is unavailable, use the following formula:

=TEXTAFTER(TEXTJOIN(“,”, TRUE, IF(ISNUMBER(VALUE(TEXTSPLIT(B3, {“,”,” “}, TRUE))), TEXTSPLIT(B3, {“,”,” “}, TRUE), “”)), “,”, -1)

Workaround with TEXTSPLIT and TEXTJOIN

Take a closer look at the formula:

  1. TEXTSPLIT(B3, {“,”, ” “}, TRUE): The {“,”, ” “} means the formula splits the text wherever there is a comma or a space. The TRUE argument ensures that Excel ignores any empty items that result from consecutive delimiters.
  2. VALUE(TEXTSPLIT(…)) convert each split item into a number. If the item is numeric (e.g., “123”), VALUE will return it as a number. If it’s not numeric (e.g., “ABC”), VALUE returns an error.
  3. ISNUMBER(VALUE(TEXTSPLIT(…))) checks if each item in the split array is a number. ISNUMBER returns TRUE for numeric values and FALSE for non-numeric ones.
  4. IF(ISNUMBER(…), TEXTSPLIT(…), “”) filters the split array to keep only numeric values. If an item is numeric (TRUE), the formula keeps it. If an item is not numeric (FALSE), it replaces it with an empty string (“”).
  5. TEXTJOIN(“,”, TRUE, …) joins the filtered array into a single string, separated by commas. TRUE ignores the empty strings (“”).
  6. TEXTAFTER(…, “,”, -1) extracts the last item from the joined string based on the delimiter “,”. Here, -1 tells TEXTAFTER to start searching from the end of the string.

GETNUMBERS function

GETNUMBERS is a user-defined function and is compatible with all Excel versions. This is the fastest way to perform the task without regex functions.

extract numbers from the end of a text string using GETNUMBERS

Download the practice file.

You can find more advanced formulas here.