How to use Excel macros to format harvested information

Often the contact information is in the usual "address label" format. The only option is to either enter the data by hand into your database, since the all the import procedures in databases require the "table" format. In this tutorial we will see how to convert and "label" format into a "table" format.

Summary

We will be using Microsoft Excel's powerful Macro and VBA features to solve this problem.

Detailed Solution

1. We have to convert the following contact information in "address label" format to a "table format".

   

2. Go to Main Menu >> Tools >> Macro >> Macros. You will see the following dialog. Type in Label_To_Table in the field for Macro name and press the Create button.

   

3. When the Microsoft Visual Basic coding screen opens up, copy and paste the following code.

   Sub Label\_To\_Table()
   ' PickEmirates.com Address Transpose Code
   ' Macro recorded 20/06/2005 by PickEmirates.com
   Do While IsEmpty(ActiveCell.Offset(1, 0)) = False
       ActiveCell.Range("A1:A5").Select
       Selection.Copy
       ActiveCell.Offset(0, 1).Range("A1").Select
       Selection.PasteSpecial
       Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
       ActiveCell.Offset(5, -1).Range("A1").Select
   Loop
   End Sub  

   

4. Close the Microsoft Visual Basic window and in you main worksheet and place you cursor on cell A1 . Now go to Main Menu >> Tools >> Macro >> Macros. Select the Macro Label_To_Table and then click on the Run button. The result will be in a table format as seen below.