QWT 3 – Export Your Gmail Contacts In Excel

About the Quick Weekend Tips (QWT) post series

QWT is a series of posts that are different from the “typical” posts that are being published in this blog. As the name implies, the posts are published during the weekends (Saturday or Sunday) and their subject is quick/short tips about various applications (Excel, PowerPoint, AutoCAD, etc.). These quick tips are accompanied by step-by-step instructions, as well as a lot of pictures.

QWT 3: Export Your Gmail Contacts In Excel

In this post, you will learn how to export all your Gmail contacts in a comma-separated values (CSV) file and afterward, how to open this CSV file in Excel and make it readable using the “Text to Columns” method. For the latter, an alternative option is given: to use the “Gmail Contacts Importer”, a custom workbook that I developed using VBA.

Export Gmail contacts in a CSV file

The procedure involves four discrete steps: Step 1: Open your Gmail account and:

1. Click the Gmail dropdown list, which is located under the Google search box in the upper left corner of your browser. 
2. From the dropdown list, select Contacts.

Step 2: In the contacts page that will open:

1. Click the More dropdown list.
2. Press the Export option.

Step 3: In the Export contacts window:

1. In the “Which contacts do you want to export?” question, select the All contacts radio button.
2. In the “Which export format?” question, choose the Google CSV format radio button.
3. Press the Export button.

Step 4: Finally, in the last window:

1. Choose the Save File radio button.
2. Press the OK button.

All your Gmail contacts will be saved into the CSV file, named “google.csv”. However, if you simply open the CSV file in Excel, it will be quite difficult to read it. In the next section, you will learn how to make the CSV file contents readable.

Text-to-column method

This method can be completed in 4 or 5 steps, depending on your choice: Step 1: Open the “google.csv” with Excel and select the entire A column.

Step 2: In the Ribbon:

1. Select the Data tab.
2. Click on the Text to Column button.

Step 3: In step 1 of the wizard that will pop up:

1. Choose the Delimited file type.
2. Press the Next button.

Step 4: In the second step of the wizard:

1. Choose the Comma delimited only. There is no need for other delimiters.
2. Press the Finish button in order for the conversion to take place OR…
3. If you want the converted text values to be placed in a different location, click the Next button (optional).

Step 5 (optional): In the final step of the wizard:

1. Choose the General data format.
2. Enter the cell destination. In that cell, the converted values will start to be placed.
3. Press the Finish button in order for the conversion to take place.

After finishing the above – 4 or 5 step – procedure, your contact list will look like this:

You can now save the CSV file as an XLSX file in order to use your mailing list like a typical Excel workbook:

1. Go to File → Save As button.
2. Choose the location you want to save the file.
3. Enter a name (i.e. Contacts List).
4. Choose the Excel Workbook format.
5. Press the Save button.

Import CSV file using the Gmail Contacts Importer

The short video below shows the 3 simple steps required in order to import your Gmail contacts (CSV file) in Excel:

1. Open the Gmail Contacts Importer workbook, enable macros, and press the Select CSV button.
2. Find the location of the CSV file on your hard disk and press the Open button.
3. Press the Import Contacts button and the contacts will be imported into the sheet Contacts.

You can download the Gmail Contacts Importer in the download section below. The Gmail Contacts Importer was based on an older code that I developed.

Downloads

The file can be opened with Excel 2007 or newer. Please enable macros before using it.