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...

VBA Code to delete worksheets

In my last post we have seen how to add worksheets to Microsoft Excel workbook using VBA code. In this post we will delete the worksheet. Now, you have to be very careful while deleting worksheet because you won’t be able to recover any worksheet that deleted by using this VBA/Macro code. I have written two different set of codes. First one will delete active sheet while other one will delete based on sheet name provided by end user. I have purposely kept the prompt so that you don’t end up loosing any data while testing code.

So, let’s move ahead with first set of VBA/Macro code.

Sub DeleteActiveSheet()

' deletes active worksheet in the active workbook

On Error Resume Next

Dim str As String

str = ActiveSheet.Name

Sheets(str).Delete

End Sub

Warning: The above will permanently delete active worksheet. Kindly be careful.

Mentioned below is a second set of VBA/Macro code.

Sub DeleteSheet()

' deletes a sheet name entered by user in the active workbook

On Error Resume Next

Dim str As String

str = InputBox("Enter the worksheet name", "Findsarfaraz")

Sheets (str).Delete

End Sub

Warning: The above will permanently delete the worksheet name provide by you. Kindly be careful.

I would suggest you to take a look at code in MS Excel file. Click here to download. To view code, press Alt + F11 keys.

If you like to read more such articles, please subscribe to my blog. Click here to subscribe, its free. Also, your comments motivates me. Please comment.

We assure you knowledge, not SPAM!

Read more on this article...

VBA code to Add worksheet

Today we will discuss a different method in VBA to add worksheet in MS Excel workbook. Now when I say different method I mean ways of adding worksheet based on your requirement. Let’s moved ahead and start with simple piece of code to complex one in steps and based on scenario.

Scenario 1: Add worksheet with default name.

Sub Addsheet()

Worksheets.Add

End Sub

Note: The above code will add a worksheet before active worksheet.

Scenario 2: Add worksheet with specific name.

Sub Addsheet()

Worksheets.Add().Name = "Sarfaraz"

End Sub

Scenario 3: Add multiple worksheets.

Sub Addsheet3()

'Add Multiple worksheets

Worksheets.Add After:=Worksheets(Worksheets.Count), Count:=4

End Sub

Scenario 4: Add worksheets at end.

Sub Addsheet4()

'Add worksheets at the end

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Sarfaraz"

End Sub

If you have any issue with replicating this code in MS Excel than download the Microsoft Excel file which I have uploaded. To view the code press Alt + F11 key on keyboard. To run the code I have place four buttons. Each button has one scenario.

Download Add Sheet example

For Free Microsoft Excel and VBA help subscribe to blog via email.

We assure you knowledge, not SPAM!

Read more on this article...

Sequence formula

Many a times you must want MS Excel to sequence the entry automatically. But thats not one of the features of MS Excel. So what you do is you enter the list than you type 1 in the top most row and drag it till last row. isn't it? This is most common practice I have seen around. Today here I will share to tricks on sequence.

In first sequence type, we will count the based on number of rows filled without checking if the value is duplicate with respect to previous row.

Formula: =IF(B6="","",COUNTA($B$6:B6))

In the above example, we are considering that we are sequencing based on column B starting from 6th row. The formula check if the value is present in B column and then counta find the count of non-blank from column B6 to respective row and returns count. Like, if you are on row 12 then formula will be  =IF(B12="","",COUNTA($B$6:B12)).  Lets take a look at illustration in image below.

Click on image below to view enlarge

In second type, the increment happens only if the text entered is different from the one in the previous cell. Like if John is entered in B2 and B3 then both will have sequence number 1. Remember the same number will be assigned only if the both entered text are in adjacent cells like B2 and B3. Also, if you are using this for data entry purpose than to fix the sequence you can sort on the column.

Click on image below to view enlarge

Formula: =IF(B5="","",IF(B5=B4,A4,A4+1))

First IF checks whether the cells has any value. If the cells is empty then it leaves sequence cell empty(in above example A column has sequence numbers) empty. Else, it checks if the current cells values is same as cell in previous row. if yes, then it returns sequence of previous cell otherwise it adds 1 to sequence of previous row and return new number in sequence. You can concatenate/combine this sequence number with any text to get unique keys like in invoices/bill or memo.

I would suggest you to download the file I have enclosed with post. That will give you more clear picture. Also, I would suggest you to use this trick in one of your MS Excel report or template. So, you can remember entire logic.

Sequence example

Thank you for spending your valuable time in reading above post and if you like to read more such tricks subscribe to blog via email.

We assure you knowledge, not SPAM!

Read more on this article...

Trunc function to remove decimal

This function remove the decimal part of number. I find this function useful only when decimal precision is not important. Like you only want to consider 2 decimal places and want to completely ignore the numbers beyond 2 decimal places or so. The most important thing about Trunc function is it does not round off, it completely removes decimal.

Syntax: Trunc(Number, Precision)

We will take few number for example to explain you how Trunc functions works. Like in all other post, I have enclosed image with example.

Click on image below to view enlarge

Also, if you face issue with replicating this example on MS Excel than download the MS Excel file with example.  I am sure you will use this function in your day to work life.

Download Example of Trunc

Thank you for spending your valuable time in reading above post and if you like to read more such tricks subscribe to blog via email.

We assure you knowledge, not SPAM!

Read more on this article...

Upper function to change case

This post is for beginners of MS Excel. This function convert all the characters to upper case(capitals). It is very useful when you have a data which is not in uniform case.  Like data received from Data entry operators.

Syntax: =Upper(Text)

Attached below is snapshot with Upper function as example. Take a look, I am sure this will help you.

Click on image below to view enlarge

 

There are other function in MS Excel which help you with cases are Lower(), Proper(). Also, the Upper function is very useful when you want to compare text from two cells. Since Exact() function is case sensitive it treats 'a' and 'A' as two different letters. I have enclosed the link to my earlier post where I have demonstrated to find cases using exact function

Check which CASE is used using Exact function

Thank you for spending your valuable time in reading above post and if you like to read more such tricks subscribe to blog via email.

We assure you knowledge, not SPAM!

Read more on this article...

Workday function

Workday function is used to find either past or future date before/after the given number of business days. The function only consider working/business days which mean while arriving to date it excludes Weekly off's and holidays. What I like most about this function is you can also provide it with holiday list you don;t want to be considered.

Now if you are thinking what will be the application of this function than let me tell you whenever I decide any dead line for project  I use this function to calculate date. This exclude all Weekends and holidays list which I provide to function. Also, this can be use to calculate delivery date or number of days of work performed.

Let's take a look at the syntax of formula below.

=Workday(StartDate, Days, Holidays)

StartDate is the beginning date

Days are the number of nonweekend and non-holidays days before or after start date which you want to use for calculating end date. Like for example, if you know that delivery of post takes 5 business days than you will input 5 to Days in formula.

Holidays: List of days you want to exclude while calculating end days. Like if you are not working on particular days than you can provide that to formula. This can be provided by using constant arrar {"A", "B","C"} or by range of cells. Remember, Saturday and Sunday are default weekends and will be considered as non working days.

Now lets take look at example of Workday function.

Click on image below view enlarge

To help you in making practical use of this formula I have made an MS Excel file with workday example and one of its application which you can download from link below. I foresee this function to be very useful when for companies who are in courier business. Also, in calculating targets for each employee and understanding time and motion calculation. Please do write to me if you like to discuss this further.

Click here to Download Workday function Example

Kindly do let us know your suggestion about post. Also, you receive free updates, add-ins, ebooks and useful macro code Click Here

We assure you knowledge, not SPAM!

Read more on this article...

VBA code, count using color

This is really funny thing in MS Excel I discovered today. Yes, a VBA code to count cells using colors. The intention behind is this post is to share VBA code and help you move ahead with VBA learning. This function is written by me. As you go below you will find formula syntax, vba code and add-ins. So, you can use add-ins on your system without replicating code and you can use this add-in all in open MS Excel file.

Syntax:  =CountColor(TargetRange,CellwithColor)

TargetRange: Range of cells which you wanted to count under which you want to count cells using criteria as another formatted cell.

CellwithColor: Single cell which filled with color which you want to count from TargetRange.

Note: It wont count cells that are formatted conditionally.

It may sound complex, but trust its very simple to use. Take a look at screen shot below where I have illustrated using CountColor.

 

Now, lets take a look a code of function which calculate count based on colors.

Public Function CountColor(target As Range, clr As Range) As Integer

Dim cnt As Integer

cnt = 0

For Each c In target
        If c.Interior.ColorIndex = clr.Interior.ColorIndex Then
            cnt = cnt + 1
        End If
Next c

CountColor = cnt

End Function

Dim cnt as Integer -> declare the integer variable used to count cells.

For each c in Target - > each c refers to one cell in target range while looping through for loop. So, if the target range is A1:A10 then while looping its goes A1, A2, A3.... ,A10.

c.Interior.colorIndex -> Colorindex returns the number for color, Interior of cell refers to color pattern and c is cell from target range. Similarly, clr.Interior.ColorIndex returns the number for color pattern in criteria.  So, if the number returned by getcolorindex method matches with number returned by getcolorindex of criteria it adds 1 to cnt.

After completing the entire range it pass the value to Countcolor.

If you still have doubts, download the count color example file. I am sure you would love to use this function.

Download Count color example

Macro are good to use when you want to process or perform operation in one MS Excel file. However, when you want to use any function/vba code independently in all open MS Excel file than add-ins are the best option. Other good thing about add-ins is they don't prompt whether you want to enable or disable code. Hence, I have uploaded the add-ins for you to use and send it across to your friends. Please feel free to download, its Free.

 Download Countcolor Add-ins

If you need help with installing add-ins, please visit our 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...