Expert Zone
XLSX + XLS repair tool

How to use Excel macros to format harvested information

By

Manju Achintha

Every business needs customers to sell to. The more prospects you have in your database, the more the chances of closing a sale. The internet is full of directories that contains the names and contact details of potential customers. The problem that most business face is getting this contact information into their database.

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.