Extract Number from String Excel

Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

How to Extract Number from String in Excel?

Splitting single-cell values into multiple cells, and collating multiple cell values into one, is the part of data manipulation. With the help of the text function in excelText Function In ExcelTEXT function in excel is a string function used to change a given input to the text provided in a specified number format. It is used when we large data sets from multiple users and the formats are different.read more “LEFT,” “MID,” and “RIGHT,” we can extract part of the selected text value or string value. To make the formula dynamic, we can use other supporting functions like “FIND” and “LEN.” However, the extraction of only numbers with the combination of alpha-numeric values requires an advanced level of formula knowledge.

This article will show you the three ways to extract numbers from a string in Excel.

Below we have explained the different ways of extracting the numbers from strings in Excel. Read the whole article to learn this technique.

Extract Number from String Excel

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Extract Number from String Excel (wallstreetmojo.com)

#1 – Extract the Number from the String at the End in Excel?

You can download this Extract Number from String Excel Template here – Extract Number from String Excel Template

When we get the data, it follows a certain pattern, and having all the numbers at the end of the string is one of the patterns.

  1. For example, the city with its zip code below is a sample of the same.


    Extract Number from String Excel Example 1

  2. We have the city name and zip code in the above example. In this case, we know we have to extract the zip code from the right-hand side of the string. But, we do not know exactly how many digits we need from the right-hand side of the string.


    Before the numerical value starts, one of the common things is the underscore (_) character. So first, we need to identify the position of the underscore character. We can do it by using the FIND method. So, apply the FIND function in excel.

    Extract Number from String Excel Example 1-1

  3. What text do we need to find in the find_text argument? In this example, we need to find the position of the underscore, so enter the underscore in double quotes.


    Extract Number from String Excel Example 1-2

  4. The within_text is in which text we need to find the mentioned text, so select cell reference.


    Extract Number from String Excel Example 1-3

  5. The last argument is not required, so leave it as of now.


    Extract Number from String Excel Example 1-4

  6. So, we have got positions of underscore character for each cell. Next, we need to identify how many characters we have in the entire text. So, we must apply the LEN function in Excel to get the total length of the text value.


    Extract Number from String Excel Example 1-5

  7. Now, we have total characters and positions of underscore before the numerical value. Therefore, to supply the number of characters needed for the RIGHT function, we must minus the Total Characters with Underscore Position.


    Extract Number from String Excel Example 1-6

  8. Now, apply the RIGHT function in cell E2.


    Extract Number from String Excel Example 1-7

  9. So, like this, we can get the numbers from the right-hand side when we have a common letter before the number starts in the string value. Instead of having so many helper columns, we can apply the formula in a single cell.


    Extract Number from String Excel Example 1-8

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

 =RIGHT(A2,LEN(A2)-FIND(“_”,A2))

It will eliminate all the supporting columns and reduce the time drastically.

#2 – Extract Numbers From Right Side but Without Special Characters

Assume we have the same data, but this time we do not have any special character before the numerical value.

Example 2

We have found a special character position in the previous example, but we do not have that luxury here. So, the formula below will find the “Numerical Position.”

Example 2-1

Please do not turn off your computer by looking at the formula. We will decode this for you.

For the SEARCH function in excelSEARCH Function In ExcelSearch function gives the position of a substring in a given string when we give a parameter of the position to search from. As a result, this formula requires three arguments. The first is the substring, the second is the string itself, and the last is the position to start the search.read more, we have supplied all the possible starting numbers of numbers, so the formula looks for the position of the numerical value. Since we have provided all the possible numbers to the array, the resulting arrays should contain the same numbers. Then, the MIN function in excelMIN Function In ExcelIn Excel, the MIN function is categorized as a statistical function. It finds and returns the minimum value from a given set of data/array.read more returns the smallest number among the two, so the formula reads below.

=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&”0123456789″))

So, now we have the “Numerical Position.” But, first, let us find the total number of characters in the cell.

Example 2-2

Consequently, it will return the total number of characters in the supplied cell value. Now, the “LEN” – position of the numerical value will return the number of characters required from the right side, so apply the formula to get the number of characters.

Example 2-3

Now, we must apply the RIGHT function in excelRIGHT Function In ExcelRight function is a text function which gives the number of characters from the end from the string which is from right to left. For example, if we use this function as =RIGHT ( “ANAND”,2) this will give us ND as the result.read more to get only the numerical part from the string.

Example 2-4

Let us combine the formula in a single cell to avoid multiple helper columns.

Example 2-5

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

#3 – Extract Number From Any Position in Excel

We have seen from the RIGHT side extraction. But this is not the case with all the scenarios, so now we will see how to extract numbers from any string position in Excel.

Extract Number From Any Position 1

For this, we need to employ various functions of Excel. Below is the formula to extract the numbers from any string position.

Extract Number From Any Position 1-1

=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9”}, “”)))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(–MID(A2,ROW(INDIRECT(“$1:$”&LEN(A2))),1))* ROW(INDIRECT(“$1:$”&LEN(A2))),0), ROW(INDIRECT(“$1:$”&LEN(A2))))+1,1) * 10^ROW(INDIRECT(“$1:$”&LEN(A2)))/10),””)

This article is a guide to Extract Number From String in Excel. We discussed the top 3 easy methods, step-by-step examples, and a downloadable Excel template. You may learn more about Excel from the following articles: –