How to Extract a Number from a String in Excel: Finding the Right Approach

When working in Excel for data cleaning, you may encounter the task of extracting numbers from alphanumeric strings. This problem is common if you have data where numbers are embedded in strings, such as product codes or survey responses.

In this article, I will show you the different methods to extract numbers from strings using formulas like RIGHT(), LEN(), and FIND() for different use cases. If you are unfamiliar with some of the basics, I recommend you take our Introduction to Excel course to build a solid foundation in Excel. You should also check out our Data Preparation in Excel to learn more about transforming raw data.

Excel Formulas to Extract Numbers from a String

Excel provides different formula-based methods to extract numbers from strings. Each method is suitable for different needs based on where the number is located in the string. If you are pressed for time, feel free to skip to the section that solves your most immediate need.

Extracting numbers from the beginning of a string in Excel

Sometimes, you may encounter scenarios where numbers are at the beginning of the string. In such cases, you will use LEFT() and FIND() to extract the numbers from the string. When you combine the functions into a formula, the numbers are extracted using the following logic:

• FIND(): Finds the position of the first letter in the string.

• LEFT(): Extracts the characters from the beginning of the string up to the position of the first letter minus one.

The first part in the formula below finds the first letter in the string. Then, the second part extracts everything before that letter, leaving just the leading numbers. If no letters exist, it returns the whole string.

=LEFT(A2, FIND(MID(A2, MIN(FIND({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"}, A2&"ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1), A2) - 1)

Extracting numbers from the beginning of a string in Excel. Image by Author.

This method is appropriate when working with datasets commonly using numeric prefixes such as order numbers, member IDs, and batch numbers.

Extracting numbers from the end of a string in Excel

If you want to extract numbers from the end of a string in Excel, you will need to use the RIGHT(), LEN(), and FIND() functions. Assume you have the following data in the Excel sheet. You will combine the functions to extract the number where:

• FIND(): Finds the position of the first digit in the string.

• LEN(): Calculates the number of characters from the first digit to the end of the string.

• RIGHT(): Extracts the characters from the first digit to the end of the string.

The formula below removes all leading text and returns the first number along with everything that follows in the string.

=RIGHT(A2, LEN(A2) - MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A2&"0123456789")) + 1)

Extracting numbers from the end of a string in Excel. Image by Author.

This method is most useful in datasets with consistent alphanumeric characters, such as product SKUs, order IDs, and serial numbers.

Extracting numbers from any position in a string in Excel

If you have a dataset where the numbers appear anywhere in the string, you will need advanced formulas using TEXTJOIN(), MID(), and ROW() to extract the numbers.

Combining these functions into a formula will extract a number from a string using the following logic: 

• ROW() and MID(): Iterates through each string character to determine whether it is numeric.

• IFERROR(): Logical test to isolate the identified numbers from other characters.

• TEXTJOIN(): Combine all extracted numeric characters into a single number string.

=TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)), MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1), ""))

Extract number from any position in a string. Image by Author.

This method is best suited when your dataset has alphanumeric characters with numbers appearing anywhere within the string. As a note, these functions are not all available in older versions of Excel. Check out our Excel Fundamentals skill track to learn more about the functions and formulas available in the latest Excel versions.

Extracting numbers by filtering out unwanted characters

Sometimes, your data is messy. Luckily, you can combine the SUBSTITUTE() and TEXTJOIN() functions. Just follow these steps:

• SUBSTITUTE(): The SUBSTITUTE() function can be used to replace all non-numeric characters in a string with a delimiter, such as a space or an empty string. For example, you can replace letters and special characters with spaces.

• TEXTJOIN(): The TEXTJOIN() function can then be used to concatenate the remaining characters (which should now only be numbers) into a single string.

=TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)), MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1), ""))

Extracting number from a string by filtering characters. Image by Author.

When You Might Need to Extract Numbers from a String in Excel

Let's now look at two small case studies to get some practice with extracting numbers from strings.

Example 1: Extracting numbers from product codes

Assume you are managing a product inventory where a unique product code identifies each item. These product codes are alphanumeric strings like "PROD1234," "ITEM5678," or "SKU91011."

So, to extract the numeric Product ID from alphanumeric product codes, we use functions based on the number's position. Remember, if the numbers are at the end, apply RIGHT(), LEN(), and FIND(). If they appear at the beginning, use LEFT() and FIND().

The example below extracts the numerical part of the product codes from the right side of the code.

=RIGHT(A1, LEN(A1) - FIND("-", SUBSTITUTE(A1, MID(A1, MIN(FIND({"0","1","2","3","4","5","6","7","8","9"}, A1&"0123456789")), 1), "-")))

Extracting numbers from product codes in Excel. Image by Author.

Example 2: Survey data with embedded numbers

Survey data sometimes contains both text and numbers like "Rated 5 out of 10," "Score: 8," or "3 stars". We will have to do a bit of work before we can calculate averages or identify trends.

Since numbers can appear anywhere in the text, a dynamic formula using TEXTJOIN(), MID(), and ROW() is required.

=TEXTJOIN("", TRUE, IFERROR(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1) * (ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1))), ""))

Extracting numbers from any position in Excel. Image by Author.

Copy and Paste Code Examples for Special Cases

For the final section, I wanted to give you several additional formulas that handle different and specific scenarios. Feel free to copy and paste, and I hope it solves any specific problem you might be having. 

Extract the first contiguous block of numbers

If your string contains multiple numeric sequences but you need only the first group, try this solution using the LET() and SEQUENCE() functions:

=LET(
txt, A2,
pos, MATCH(TRUE, ISNUMBER(--MID(txt, SEQUENCE(LEN(txt)), 1)), 0),
len, MATCH(FALSE, ISNUMBER(--MID(txt, SEQUENCE(LEN(txt)-pos+1)+pos-1, 1)), 0) - 1,
VALUE(MID(txt, pos, len))
)

Extracting the first contiguous block from a string in Excel. Image by Author.

Extract numbers enclosed in parentheses

For cases where numbers appear inside parentheses, use:

=MID(A2, FIND("(", A2)+1, FIND(")", A2)-FIND("(", A2)-1)

Extract numbers enclosed in parentheses in Excel. Image by Author.

Extract numbers after a specific character

When your data uses a colon as a delimiter (for instance, "Score: 8"), capture the numbers with:

=TRIM(MID(A2, FIND(":", A2)+1, LEN(A2)))

Extract number after a special character in Excel. Image by Author.

This pulls everything after the colon. Wrap the result with VALUE() if you need a numeric value. Replace the colon if you have a different delimiter.

Extract all numbers and combine them into a single value

To remove all non-digit characters and combine every numeric character in a string, use this:

=TEXTJOIN("", TRUE, IFERROR(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)*1, ""))

Extracting all numbers and combining them into a single value in Excel. Image by Author.

Tip: In older versions of Excel, enter this as an array formula (Ctrl+Shift+Enter).

Extract numbers including decimal points

For numeric strings that may include a decimal (e.g., "Price: 45.99"), use this formula to retain both the digits and the decimal point:

=TEXTJOIN("", TRUE, IFERROR(
  IF(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)=".", ".",
     IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)),
        MID(A2, ROW(INDIRECT("1:" & LEN(A1))), 1),
        "")
  ),""))

Extract numbers including decimal points in Excel. Image by Author.

Note: This formula assumes only one decimal point is present.

Conclusion

Extracting numbers from strings is an important technique for data cleaning. As a data analyst, I encourage you to learn how to apply the different formulas discussed in this article.

If you want to advance your Excel skills, I recommend taking our Data Analysis in Excel and Data Visualization in Excel courses to master data analysis and presentation. Also try our  Financial Modeling in Excel course which can open a lot of doors.