EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources TEXT and String Functions in Excel Extract Numbers From String
Secondary Sidebar
Excel Functions
  • TEXT and String Functions in Excel
    • Excel CODE Function
    • Excel Find
    • TRIM Formula in Excel
    • Excel CHAR Function
    • LEFT Formula in Excel
    • Opposite of Concatenate in Excel
    • Concatenate Strings in Excel
    • Extract Numbers From String
    • VALUE Formula in Excel
    • Left TRIM in Excel
    • Substring Excel Function
    • SUBSTITUTE Function in Excel
    • Excel VALUE Function
    • Excel REPT Function
    • Excel TRIM Function
    • Excel CLEAN Function
    • Excel EXACT Function
    • PROPER Function in Excel
    • Excel LEFT Function
    • MID Function in Excel
    • LEN Function in Excel
    • CONCATENATE Function in Excel
    • SEARCH Function in Excel
    • RIGHT Function in Excel
    • FIND Function in Excel
    • TEXT Function in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Online COURSE
  • Online Excel Data Analysis Training

Extract Numbers From String

By Madhuri ThakurMadhuri Thakur

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.

Start Your Free Excel Course

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

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:

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,452 ratings)

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
Popular Course in this category
Excel Training (23 Courses, 9+ Projects)
  23 Online Courses |  9 Hands-on Projects |  110+ Hours |  Verifiable Certificate of Completion
4.9
Price

View Course

Related Courses

Excel Advanced Training (16 Courses, 23+ Projects)4.9
Excel Data Analysis Training (17 Courses, 8+ Projects)4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Free Excel Course

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

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

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

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Extract Numbers from String Template

EDUCBA

Download Extract Numbers from String Template

EDUCBA

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

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more