Sat 10 Oct 2015

Article 27 - Remove Leading Apostrophe

By |Saturday, October 10th, 2015|Categories: Articles, VBA|Tags: , , , , , |4 Comments

Leading apostrophe is one of the widely used features of Excel. This is mostly used to make a cell treat its content as a character. It can also come through import of .csv files from various third party systems or data entries can be made with leading apostrophes. If you enter a number of more than 15 significant digits, Excel will immediately convert this to scientific notation. But there are business situations where you deal with numbers larger than 15 digits like credit card numbers which may be 16 digits. Now if you input credit card numbers as numeric, you will not be able to capture all 16 digits, so a solution is to enter them with leading apostrophe. It forces the cell to treat the entry as character and in character format you can show these 16 digits.(You can enter 32,767 characters in a cell though only 1024 characters will be displayed)

(more…)

Sat 03 Oct 2015

Tips & Tricks 111 - Extract URLs from Hyperlinks

By |Saturday, October 03rd, 2015|Categories: Tips and Tricks, VBA|Tags: , , , , , |0 Comments

In Excel, sometimes you encounters words which are actually Hyperlinks. Say a cell contains a word Microsoft and you notice that this is in Blue and when you click it, it takes you to http://www.microsoft.com. Another cell contains, the word Latest Yahoo Movie Blockbuster and when you click it, it takes you to http://www.yahoo.com/movies#LatestBlockbuster

Now, how to extract this. If there are very few entries like this, you can simply right click the cell > Edit Hyperlink > Copy the URL

But, you have a bunch of entries like this and you need to extract the URLs. For this, the only method is to use a simple piece of code. Don't be frighten even if you have never touched VBA before. Simply follow the following steps -

1. ALT+F11 or Right Click on tab name > View Code to open VBA window.
2. Now locate your workbook on the left side in Project Explorer window though it should be visible by default.
3. Right click on your Workbook Name in Project Explorer window > Insert Module
4. Double click on the module inserted and copy and paste following code

Function GetURL(Rng As Range) As String
    If Rng(1).Hyperlinks.Count Then
        GetURL = Rng.Hyperlinks(1).Address
        If Len(Rng.Hyperlinks(1).SubAddress) > 0 Then
            GetURL = GetURL & "#" & Rng.Hyperlinks(1).SubAddress
        End If
    Else
        GetURL = ""
    End If    
End Function

5. Now, in your sheet you can use GetURL as a function like a regular function. If your Hyperlink is in A1, you can write =GetURL(A1) to extract

Credit - http://blog.contextures.com/archives/2010/12/13/get-the-url-from-an-excel-hyperlink/

Sat 26 Sep 2015

Article 26 - Converting Amount into Words (Indian Currency - Rupees and Paise)

By |Saturday, September 26th, 2015|Categories: Articles, VBA|Tags: , , , , , , , |10 Comments

Below is the VBA function where you can convert a given amount into Words on the basis of Indian Currency. India utilizes a system which is based on Hundred, Thousand, Lakh.....unlike English system which is based on Hundred, Million, Billion..(Indian Numbering System)

Below are numerical equivalent of Indian words

Lakh - 1,00,000
Crore - 1,00,00,000
Arab - 1,00,00,00,000 (more…)

Sat 19 Sep 2015

Tips & Tricks 109 - Macro to Change between A1 and R1C1 Notations

By |Saturday, September 19th, 2015|Categories: Tips and Tricks, VBA|Tags: , , , , , , |1 Comment

Sometimes, you find that all of a sudden your column headings are changed into numbers and your formulas are Rs and Cs. And you need to change them back to alphabetical columns.

The option to do it very simple -

File > Excel Options > Formulas > Uncheck R1C1 Reference Style

1

But if that happens very often, you can use a macro which you can click and toggle between A1 and R1C1 style effortlessly. The macro code to do it one line only -

Sub ChangeReferenceStyle()
Application.ReferenceStyle = xlA1 + xlR1C1 - Application.ReferenceStyle
End Sub

Credit - Rick Rothstein (http://blog.contextures.com/archives/2009/12/04/excel-vba-switch-column-headings-to-numbers/)

The above link also talks about how to add the macro to QAT so that it is always available to you.

Sun 02 Aug 2015

Article 19 – How to Check if a cell contains a date

By |Sunday, August 02nd, 2015|Categories: Articles, VBA|Tags: , , , , |5 Comments

Checking for dates in Excel can be a very interesting problem as Excel stores dates as numbers. For example, 4-Jul-15 is stored as 42189.

To elaborate further on this, 1-Jan-1900 is treated as 1. The difference between a date and 1-Jan-1900 +1 is the serial number of that date. hence, in case of 4-Jul-15, it is 42189.

Hence, if you write 42189 and 4-Jul-15 in two different cells, Excel has no built-in mechanism to differentiate between these two.

(more…)