VBA code, extract Number from Text

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.

Click here to download Add-in

I would love to listen to you. Please write your comment below.

Subscribe here, its free.

We assure you knowledge, not SPAM!

Read more on this article...

VBA function to Concatenate

In this post, let's take a look at VBA code/function to concatenate string. Like in Microsoft Excel concatenate function you cant provide function with range. So you have to provide function with each cell at a time which is time consuming.  The best solution to help

VBA Code

Function VBAConcatenate(target As Range, delimiter As String) As String
Dim str As String

For Each c In target
    str = str + delimiter + c
Next c

If delimiter = "" Then
    VBAConcatenate = str
Else
    VBAConcatenate = Right(str, Len(str) - 1)
End If

End Function

VBAConcatenate require following parameters

Target: Target is range of cell you wish to concatenate.

Delimiter: Delimiter is any separator you want to use between concatenated text.

Example 1: You have A, B, C and D in range C6:C9 and you want the results as ABCD than you will use VBAConcate in following manner.

Formula: =VBAConcatenate(C6:C9,"")  will return ABC

Click on image below to view enlarge

Example 2: You have A, B, C , D and E in range A6:E6 and you want the results as A|B|C|D|E  than you will use VBAConcate in following manner.

Formula: =VBAConcatenate(A6:E6,"|" )  will return A|B|C|D|E

Click on image below to view enlarge

Note this is very useful when you want to type a SQL query. In a query you need field name separated by comma. I use this function to get me list of field names in query.

You can also download the Microsoft Excel file with VBAconcatenate examples here. Just click on link below, to view code press Alt + F11

Click here to Download

The only issue with user define functions like VBAConcatenate is they remain limited to that MS Excel workbook where they are written. To use then in all workbooks on your system, you will have to convert this function into add-ins. So, once you install them on your computer you can use VBAConcatenate than in any workbook. You can download VBAConcatenate add-in here.

Click here to download Add-ins

If you need help with installing add-ins, please visit my earlier post How to install Add-ins.

Thanks for spending time in reading post. Request you to leave your comments. Also, you can receive updates, add-ins and e-books, its free.

We assure you knowledge, not SPAM!

Read more on this article...

Book Mark Add-in

Huge reports with more than 5 sheets are very difficult to browse. Also, in spite of making very professional report you will it confusing. So, solution to avoid confusion is to add book mark sheet with link to each Microsoft Excel worksheet. This worked in my case with large number of reports. But the process is very hectic. Like adding a shape, adding text to shape and than linking it to right worksheets which no one would like to do for each reports you make. Hence, I developed an MS Excel add-in which will automatically does these all for you. Take a look at the video below how it works.

How to use Bookmark Add-ins

This add-ins cost only 5$. If you like to receive this add-in click on button below to pay via Paypal. We will send you add-ins via email. Instructions to installation are available on my other post How to install add-ins.

If you want we will customize this add-ins to add your company name on Bookmark sheet, colors and font as per your need and customization will take only additional 5$.

Note: This add-ins is tested to work with MS Office 2003/2007. In case if you face any issue you can contact us and we will provide you support via email/chat.

Please suggest us your comments and feedback. Also, you can subscribe my blog. It’s free.

We assure you knowledge, not SPAM!

Read more on this article...