Extract Number from String Excel

Last Updated :

-

Blog Author :

Jeevan A Y

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

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 excel "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.

Table of contents

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

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

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

 =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 excel, 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 excel 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 excel 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),””)