Extracting numbers from alphanumeric text
We have seen such posts on the net:
I have a spreadsheet that contains a column with street addresses, including numbers. I need to extract only the numbers from that column and put them in another column. How do I do that? To complicate things, the adress strings can look like this:
but also like this:
DE GRENT 16 -18
A V ROYENSTRAAT 106 A
Thanks for any help!
Today we’ll learn how to extract numbers from alphanumeric text.
The process of number extraction involves the following steps: First we need to break the alphanumeric text into seperate characters using the mid function. Here the numbers will also be extracted as text. We can multiply these numbers by 1 to convert them into numbers.
Next we find out whether there is a number in the text by using the function isnumber. A ‘True’ result indicates that the value discovered is a number.
We then find the position of the number using the match function. Remember ‘match’ searches for an item in another column based on a condition in another column. Like when you search for a name in a directory to find its corresponding phone number. Since match is an ‘array’ function you must complete the calculation by pressing ‘CTRL+SHIFT+ENTER’ from the keyboard after entering the formula. The first number in our data ‘dineshtakyar180170’ is found at position 13.
Finally we count the numbers using the count function. Remember count can only count numbers and we must multiply our extracted numbers by 1 because they are still ‘text’.
Once we integrate the above individual formulas into one single formula, we can easily extract the numbers. Since the extracted numbers are still text, these numbers should again be multiplied with 1 to convert them into numbers if you wish to add, multiply or do arithmetic operations with the extracted numbers.
To get proper results you must press ‘CTRL+SHIFT+ENTER’ because the formula has ‘array’ character.
Now let’s write some more examples and extract the numbers as shown in the Excel training video.
A special thanks to MVP Ashish Mathur whose article helped to understand the topic better.