Extract Numbers from Text using Excel VBA [Video]

Last week we discussed how to extract numbers from text in Excel using formulas. In comments, quite a few people suggested that using VBA (Macros) to extract numbers would be simpler.

So today, lets learn how to write a VBA Function to extract numbers from any text.

Using VBA Function to Extract Numbers from Text in Excel

When using VBA to scan a text for number, the basic approach is like this:

1. Read each character in a given text
2. See if it is number
1. If so, extract it
3. Continue with next character
4. Convert the extracted characters to a number
5. Return that number

While this works fine, it also has some limitations.

For example, with above approach, A text value like “US $313,00.00”  will be extracted as 3,130,000 not as 31,300.00

Depending on your data, you may have many such peculiarities. For example, here are 4 situations I ran in to:

Handling decimal points & thousand separators during extraction

When it comes to decimal points & thousand separators there are 2 conventions:

1. 61,000.30 (Regular)
2. 61.000,30 (European)

We do not need special treatment for regular format (61,000.30) as Excel & VBA are capable of dealing with these numbers by default.

To check if a text has European format number, we have to see if . occurs before ,

(Note: this method is not fool-proof, but should work well for most situations)

This can be done by using LIKE statement,

if text like "*.*,*" then
european = true
else
european = false
end if

Writing our getNumber() VBA Function

Once we put all these ideas together, we will have our getNumber() function. Watch below video to understand how to extract numbers from text using Excel VBA.

[Watch this video on our Youtube channel]

Download Number Extraction VBA Function

Click here to download the Extract Numbers using VBA workbook.

View code module to understand how getNumber function works.

Do you use VBA to extract numbers?

I often use VBA to clean raw data. Earlier I mentioned about cleaning phone numbers & spelling mistakes. I think simple functions like getNumber() can save us tons of time & let us focus on the important task – analyzing data.

What about you? Do you use VBA to clean data? What techniques & ideas you rely on? Please share your thoughts using comments.

New to Excel VBA? Take our crash course

Are you new to Excel VBA? If so, go thru below links to take our FREE VBA Crash course.

1. What is VBA & Writing your First VBA Macro in Excel
2. Understanding Variables, Conditions & Loops in VBA
3. Using Cells, Ranges & Other Objects in your Macros
4. Putting it all together – Your First VBA Application using Excel
5. My Top 10 Tips for Mastering VBA & Excel Macros

If you want more,

I know you are thirsty for more. Why not join our Online VBA Classes and learn Excel VBA in step-by-step manner. Click here to know more.