During this post I will try to share VBA/Macro code to extract number from the text. Also, we will discuss the code to help you in understanding how to code in VBA. After all the aim is to make you enable write VBA code. Though in most of my post I write this at the end. This time I am advising you in middle to subscribe yourself to this blog. Also, your comments motivates us.
Let's move ahead with first understanding the application/scenario under which you can use this code. Take an example where you Microsoft Excel file where you have series of number where text is between number and you are only interested in numbers and not next. If the text is on fixed place you can remove that using LEFT() , RIGHT() or MIDDLE() function. But if the numbers and text are placed in cell where you are not sure number of text character and position of text. VBA code is solution to extract number.
VBA code to extract Number from Text
Function ExtractNumber(Target As Range) As Variant
Dim i As Integer
Dim str1 As String
For i = 1 To Len(Target)
If IsNumeric(Mid(Target, i, 1)) Then
str1 = str1 + Mid(Target, i, 1)
End If
Next i
ExtractNumber = str1
End Function
Logic
For loop scan through the entire text like if you pass on the text Ae98rc243cd it will loop to entire text with one character at a time. LEN(Target) provide length to For Loop. Mid(Target,i, 1) picks one character at a time. Isnumeric(Mid(Target,i,1) checks if the character is numeric or not, if it's numeric then it concatenate/join the numbers into str1 using code str1 = str1+Mid(Target, i, 1).
I am sure this is not very difficult to understand. If you still face any issue, feel free to contact via email. I will try to reply all emails via email or via post.
Take a look at image below which will guide you how to use this function on MS Excel worksheet.
The above code can be copied to module to use it. However, you are facing an issue with copying VBA code. Please download the file and to view code press Alt + F11 key.
Click here to download Extract Number example
Now, to use this code/VBA function in all Microsoft Excel file. You can download Add-ins which has this function embedded. There is another post on this blog to help you with installing Add-ins.
I would love to listen to you. Please write your comment below.
We assure you knowledge, not SPAM!
Read more on this article...