Function Problems

How to Extract the Final Number from a Text String in Excel

Muhammad Talha Oct 16, 2026 871 views

Question details

The user needs to extract a number of variable length that is located at the very end of a text string.

How to Extract the Final Number from a Text String in Excel

Product: Excel
Device & OS: not provided
Scenario: Extracting trailing numeric values (e.g., amounts) from descriptions or sentences where the number of digits varies, making fixed-length extraction impossible.
Observed behavior: The goal is to accurately isolate the trailing substring based on a delimiter or prefix, and convert it into a usable numeric value.

Before you start

Identify the delimiter separating your text from the final number, such as a space or a specific preceding word (like 'VAT:'), as this determines which formula will work best.

Solution 1Recommended

Use the TEXTAFTER Function for Space-Delimited Strings

Best for modern spreadsheet software where the final number is separated from the preceding text by a space.

The TEXTAFTER function is highly efficient for isolating text after a specific delimiter. By using a negative instance number, it forces the formula to search from the end of the string backwards.

Select the output cell

Click on the empty cell where you want the extracted number to appear (for example, cell B2).

Enter the formula

Type =1*(TEXTAFTER(A2," ",-1)) into the formula bar and press Enter. The '1*' portion forces Excel to convert the extracted text string into a mathematical numeric value.

Apply to the remaining data

Click and drag the fill handle at the bottom-right corner of the cell downwards to apply the formula to the rest of the rows in your dataset.

Use the TEXTAFTER Function for Space-Delimited Strings

Version Compatibility: TEXTAFTER is available in Microsoft 365 and newer versions. If you are using an older version of Excel, please refer to the RIGHT and FIND method below.

Solution 2

Use RIGHT, LEN, and FIND for Specific Prefixes

Ideal for older versions of Excel or when the number follows a specific, consistent text prefix (e.g., 'Amount Ex VAT:').

Efficient Spreadsheet Data Processing

Extract Text and Numbers Easily with WPS Spreadsheet

WPS Office provides a powerful, free Spreadsheet tool that fully supports standard Excel formulas, including RIGHT, LEN, FIND, and modern text functions. Easily handle complex data extraction tasks with high compatibility and a user-friendly interface.

1. Open your document in WPS Office: Launch WPS Office and open your spreadsheet file containing the text strings.
2. Apply the extraction formula: Select a blank cell and input your preferred extraction formula, such as =1*(TEXTAFTER(A2," ",-1)).
3. Format as Number: Right-click the result cell, select 'Format Cells', and choose 'Number' to ensure the extracted value is ready for further calculations.

100% compatible with Microsoft Excel formulas and .xlsx file formats.Lightweight application that runs smoothly and quickly on Windows, Mac, and Linux.Built-in intelligent data processing features and intuitive UI to boost your daily productivity.

microsoft office alternative - wps office

Frequently Asked Questions

Why is the extracted number treated as text and cannot be summed?

Text extraction functions like RIGHT, LEFT, or TEXTAFTER always return text strings by default, even if they look like numbers. To convert them to usable numbers, wrap your formula in the VALUE() function, or multiply the entire formula by 1 (e.g., =1*RIGHT(...)).

How can I extract numbers from a string if there are no spaces or delimiters?

If the number lacks a clear delimiter and is directly attached to letters (e.g., 'Invoice12345'), you will need to use an array formula or a combination of functions like MIN, FIND, and ROW to locate the first numeric character's position, then extract from that point onward.

Does the TEXTAFTER function work in older versions of Excel?

No, TEXTAFTER is a modern dynamic array function only available in Microsoft 365 and Excel for the Web. For Excel 2019 or older standalone versions, you must use alternative text combinations like RIGHT, LEN, and FIND.

Excel & Data Problems