How to convert address lists into Excel tables

Introduction

Your boss has just given you a text file crammed with a list of 15,000 plus businesses with their address and phone number that she needs you to put into tables by the end of the week. If you're on Windows, you could open up Excel and start to copy and paste line by line each section from Notepad onto your spreadsheet according to column headers: Businesss Name, Address, City, State, Zip, Phone, Fax etc. Ten minutes of this and you've got about 25 records done. This is going to take a long time!

Is there a simpler way to drop a big list of addresses onto Excel? Well, the good news is that Excel can do the heavy lifting and save you time for more important things -- like lining up at Starbucks for a double-shot Americano.

This article will explain how to import a text file with addresses into Excel and use the Text-to-Column function to quickly arrange them into the correct headings. I'll use a case example to illustrate. I'll also give you some tips on how to handle tricky formats and cull unwanted data from your spreadsheet table.

Sample of Directory of Burger King

A partial listing of BK in Los Angeles
A partial listing of BK in Los Angeles

The Burger King project

Okay, now here's a worthwile project...

Your manager has asked you to create a table listing of all the Burger King's in California. If it was only Los Angeles, you'd be looking at a listing of 43 sites. You could reasonably copy and paste each element onto each cell with relative ease and accuracy. That's not bad -- but what if she gave you a file that had all BK's in America? Now that's quite an onerous task to do by hand.

The table will have the following headings for each column: Street, City, State, Zip. Notice the sample output text file above lists the sites in three columns and each address record takes three rows (a 3X3 formatting) separated by spaces. There's probably a bunch of ways to build an Excel table including writing a macro to copy and paste the data -- but you need to be very confident it will work reliably. My goal is to teach you a simple and straight-forward way to accomplish this task confidently. If you practice enough, you'll find novel variations to handle more trickier formats (for example: first column, address; second column, manager's name and hours; third column, drive-thru and play area info).

Using the [text-to-column] function on Excel is easy and it will save you time and permit accurate data-entry. It won't be a completely automated process but at least you'll have the satisfaction of monitoring your work until completion. There are plenty of [text-to-column] tutorials you can Google. I'll save you some time and give you some YouTube clips below that visually explain this function for 2003 and 2007 versions.

Please review the video before I move onto how we handle address formats specifically. Then I'll walk you through the general steps before I show you a pretty cool method of reproducing all the addresses into separate columns.

Text-to-column using Excel 2003

Text-to-column using Excel 2007

Step 1: importing the text file

Before we begin. Check how large your text file is. Earlier versions of Excel cannot handle files having more than 65,535 rows. If your file is that large, you will need to split the files into chunks and create a separate sheet. I'll give you a link for how to do this at the end of the article.

When you import text files, select [open file] and browse for your text file. Be sure to choose the drop down menu to browse all files, otherwise you'll end up seeing Excel files only. Once you open it, Excel will automatically open a window called [text import wizard] that happens to behave exactly like the [text-to-column] function.

Remember the file is formatted 3X3. So let's tackle the easiest task, the three columns. Pick [fixed width] and adjust the bar so it lines up with each addresses. If you had extraneous data like page numbers you can isolate it and [skip] the column when you import.

You should have a table that would look like below.

Table 1

Col 1 
Col 2 
Col 3 
1011 North Western Avenue
1673 103rd Street
233 W Washington Blvd
Los Angeles, CA 90029-2309
Los Angeles, CA 90002-2924
Los Angeles, CA 90015-3543 
2309 (323) 467-8334
(323) 564-8594
(213) 749-0022
 
 
 
1106 Cornwell Street
1742 S. La Cienaga
2511 South San Pedro Street
Los Angeles, CA 90033-1415
Los Angeles, CA 90035-4602
Los Angeles, CA 90011-1519
(323) 223-4821
(310) 836-5065
(213) 748-2821

Step 2: rearrange columns

Since the three columns are have similar records (all are addresses) then it makes sense to cut and paste the last two column onto the end of the first column. Be sure to match the spaces between addresses.

For more complicated formats like the example I gave earlier, I'd keep them is separate columns for each heading.

Now our list of BK addresses are along the first column. But each address has elements in rows we need to cut and paste onto each new column. There's a neat way to accomplish this because each address record is consistently three rows.

Before moving to the next step, insert a few blanks rows at the top.

Table 2

Address
City, State, Zip 
Phone 
 
 
1011 North Western Avenue 
 
1011 North Western Avenue 
Los Angeles, CA 90029-2309  
1011 North Western Avenue
Los Angeles, CA 90029-2309  
(323) 467-8334  
Los Angeles, CA 90029-2309
(323) 467-8334
 
(323) 467-8334
 
1106 Cornwell Street
 
1106 Cornwell Street
Los Angeles, CA 90033-1415
1106 Cornwell Street
Los Angeles, CA 90033-1415
(323) 223-4821
Los Angeles, CA 90033-1415
(323) 223-4821
 

Step 3: copy and shift up

Table 2 illustrates a simple copy and paste onto each new column.

Notice how as you move along the row of the first address line (1101 Norh Western Avenue) that we have each address info in columns. I've marked the headers so we know what each column should contain. We'll need to cull out the redundant lines by sorting.

So highlight all the columns you need to sort and sort [A-Z] the City, State, Zip column. I picked this one because it has the most uniform data -- Los Angeles.

All you need to do is delete all rows that don't have Los Angeles in the center column. See Table 3 below for final result. I've added a link on sorting below too.

[Text-to-column] could also be used to split up the City, State, Zip into separate headings. Play around with this and I think you'll find it really easy to master.

Table 3

(click column header to sort results)
Address   
City, State, Zip   
Phone   
1011 North Western Avenue 
Los Angeles, CA 90029-2309 
(323) 467-8334  
1106 Cornwell Street 
Los Angeles, CA 90033-1415 
(323) 223-4821 
 
 
 

Conclusion

Oftentimes, administrative workers are asked to do clerical tasks like re-enter or copy data from one file format onto another. Excel is great for sorting and organizing data into tables. These tables can be used to generate reports and act as a database for instance. Because Excel is a common business application found on most office computers, a great deal of lists and reports are created using it.

Most of the time, I get e-mailed text files needing conversion to tables or lists. Some text files appear difficult or next to impossible to import into Excel because of unusual formatting. The address format definitely belongs in this category.

Hopefully, you were able to follow this article and mastered a useful skill. I'd like to thank my sister for calling me up during the Christmas holidays and mentioning her data-entry predicament. She was behind on entering a large list of donor's names and addresses for an organization before the year end. The fact that massive hours of work was cut down to about 15- to 30-minutes with a simple Excel function means considerable savings and a productivity boost at the office.

My sister thanked me back from saving her from carpal-tunnel-syndrome. Since I forgot to send her a gift, I offered my advice as her special gift from me...

Sorting columns in Excel

Comments 5 comments

Laura du Toit profile image

Laura du Toit 6 years ago from South Africa

Excellent Hub. Congratulations on your well-deserved nomination for Hubnuggets!


ripplemaker profile image

ripplemaker 6 years ago from Cebu, Philippines

Whew...I know I will take time to learn this one but learn it I must. hehe okay, bookmarking for future use and reference. Congrats for your hubnugget nomination, to vote and see the hubnugget happenings, this way please: http://hubpages.com/community/Lets-Make-a-HubNugge


Ray Chua 6 years ago Author

Thanks for the kind words and the nomination. It's my first attempt at writing to a wider audience. I must admit that this topic may be tougher to understand and explain. If you do find anything confusing, by all means comment it and I will try to respond back.


horses_rock_166 profile image

horses_rock_166 6 years ago

Good job!


Michelle 6 years ago

Thank you! You just made my day so much easier!

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working