EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources TEXT and String Functions in Excel Extract Numbers From String
 

Extract Numbers From String

Madhuri Thakur
Article byMadhuri Thakur

Updated August 16, 2023

extract numbers from string

 

 

Extract Numbers From String (Table of Content)
  • Introduction to Extract Numbers From String
  • How to Extract Numbers from String in Excel?

Introduction to Extract Numbers From String

Excel is a tool that is very versatile and compact at the same time. When it comes to extracting certain characters from a text string, it provides a range of formulas such as LEN, RIGHT, LEFT, SEARCH, using which individually or in combination with each other, we can find out the character required. However, when it comes to extracting numbers from a string that contains a mixture of characters and numbers, Microsoft Excel fails to give any concrete formula for the same. However, it is not impossible at all to extract numbers from a string. It is definitely not as easy as extracting characters from string but at the same time not impossible. All you need to have is patience, some combination of different functions nested within each other. It will take you through. Let’s take some examples and see how we can extract numbers from a text string.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

How to Extract Numbers from String in Excel?

To Extract Numbers from String is very simple and easy. Let’s understand how to Extract Numbers from String in Excel with some examples.

You can download this Extract Numbers from String Template here – Extract Numbers from String Template

Example #1 – How to Extract Numbers Positioned from Right

As you might have seen while extracting characters from a string, the most important thing we should know is the position of the last character. Once it is found, we can use the RIGHT function to extract the characters from the text. We would like to know the first digit/number position in the string on similar lines.

Suppose we have data as shown below:

extract no from string 1-1

Use the following formula in cell B2 to get to know from where the extraction/split starts. The formula is given below results in giving the position of the first number appearing in the mix string.

extract no from string 1-2

After using a formula in cell B2, the output is shown below.

extract no from string 1-3

Drag this formula up to row number six. You’ll see the results as below:

extract no from string 1-4

Numbers in each cell of column B denote the first digit’s position appearing in a string present at each cell of column A.

Use a combination of RIGHT and LEN functions to extract the numbers out of the string in column C. For example, put the following formula in cell C2 and press Enter key.

extract no from string 2-1

After applying a formula to all the cells, you should get an output as follows.

extract no from string 2-2

Here in this formula, we wanted to extract only the numbers from the string present in cell A2. Also, all the numbers are on the right-hand side of the cell. Therefore, we use the RIGHT function initially with A2 as an argument. In this function, we used the LEN function, which takes a length of A2 and subtracts B2 (which contains the first position of number in the string). We also add 1 in it so that the first digit also gets included under the result.

Drag the formula until row number five of column C.

extract no from string 1-5

If you could have noted in the results present in column C, all the numbers are left-aligned. This means those are being stored as text in column C. We have used formulae such as RIGHT and LEN to find out the numbers from the given text. So, in order to convert all the text values present in each cell of column C, follow the next step.

Example #2 – VBA Code to Extract Numbers through Mixed String

What we just did can be done on similar lines using a Macro/VBA code as well. The advantage of macro would be that it will automate the system, and once the code is done and set, you don’t need to edit it again. Just use the same macro again and again for a different set of mixed strings to extract the numerical values.

Go to the Developers tab and click on Visual Basic.

 VBA Code 1

Click on the Insert tab present on the upper ribbon and open a new Module through it.

 VBA Code 2

Define a new sub-procedure under which we can store the macro.

 VBA Code 3

Now, copy the following code and paste it under the given sub-procedure.

 VBA Code 4

Here, the code is divided into three parts –

  • First is the initialization of the variable, which is going to hold the numerical values extracted from the string.
  • Under the second part, you need to run through the range under which the values are present and extract numbers.
  • Third and the final part contains the loop, which allows you to extract numbers from a given string and stores them under a given column.

Run this code by hitting the F5 or Run button placed on the upper pane.

 VBA Code 6

You’ll see the output under column B of your worksheet. Which only contains numbers extracted from the actual string.

 VBA Code 7

  • This VBA code’s benefit is that it works on any string with having a position of numbers anywhere. In between. While the formula used in the first example is limited to the strings in which the numbers are at the right of the string.
  • These are the two ways using which we can capture and extract the numbers from a given mixed string. I assure you there are many more. But these two are the ones this article has covered.

Let’s wrap the things with some things to be remembered.

Things to Remember About Extract Numbers From String

  • In the first example, all we used are text functions like RIGHT and LEN. The results provided by these formulae will be text only (even numbers would be stored as texts). If you want them to convert into actual numerical values, use the VALUE function to enter all the formulas.
  • The formula method is a bit tricky but can work on thousands of cells at the same time. However, it lacks versatility. Like the one we used in the first example. In addition, that formula only works when your numerical values are on the right-hand side of your actual string.
  • Using VBA macro, you can get the results just in a single click. Irrespective of the position where your number belongs in an actual string. This is the advantage of macro (But you need to be decent enough to write a macro to automate the processes).

Recommended Articles

This is a guide to Extract Number From String. Here we discuss How to Extract Number From String in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles.

  1. Substring Excel Function
  2. Concatenate Strings in Excel
  3. VBA String
  4. VBA Replace String

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download Extract Numbers from String Template

EDUCBA Login

Forgot Password?

EDUCBA

Download Extract Numbers from String Template

EDUCBA

डाउनलोड Extract Numbers from String Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW