I have copied into Excel an extensive list of business names, addresses, and phone numbers from a yellow pages type site. I would like to split each entry into columns. The problem is that each entry is not always in the same format, although a majority of them are. I've found a couple of other similar requests by searching here, but haven't been able to get a solution for my problem.
There are no empty rows between the business entries. There seem to be three different formats. One with three rows: name, address/city/state/zip, phone. Another with 4 rows: name, address/city/state/zip, phone1, and phone2. And another with two rows: Name, city/phone. See my example below. Can somebody help me split these addresses out? I have several hundred to split. Is there a macro or formula I can use?
edit: The software Listgrabber does exactly what I want, but is quite pricey at $249. If this can't be done in Excel, does anybody know of a similar program like Listgrabber?
There are no empty rows between the business entries. There seem to be three different formats. One with three rows: name, address/city/state/zip, phone. Another with 4 rows: name, address/city/state/zip, phone1, and phone2. And another with two rows: Name, city/phone. See my example below. Can somebody help me split these addresses out? I have several hundred to split. Is there a macro or formula I can use?
edit: The software Listgrabber does exactly what I want, but is quite pricey at $249. If this can't be done in Excel, does anybody know of a similar program like Listgrabber?
| BGLocate.xls | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | To_Split | Name | Street | City | State | Zip | Phone1 | Phone2 | ||
| 2 | Accounting Unlimited, Inc. | Accounting Unlimited, Inc. | 1724 Rockingham Ave | Some City | KY | 42104 | 270-555-8181 | |||
| 3 | 1724 Rockingham Ave Some City KY 42104 | |||||||||
| 4 | 270-555-8181 | |||||||||
| 5 | Avery & Smallwood CPAs | Avery & Smallwood CPAs | 804 Chestnut St | Some City | KY | 42101 | 270-555-1117 | |||
| 6 | 804 Chestnut St Some City KY 42101 | |||||||||
| 7 | 270-555-1117 | |||||||||
| 8 | Bkd LLP | Bkd LLP | 400 E Main St | Some City | KY | 42101 | 270-555-0111 | |||
| 9 | 400 E Main St Some City KY 42101 | |||||||||
| 10 | 270-555-0111 | |||||||||
| 11 | Blankenship Albert E CPA | Blankenship Albert E CPA | 370 Cal Batsel Rd | Some City | KY | 42104 | 270-555-1040 | |||
| 12 | 370 Cal Batsel Rd Some City KY 42104 | |||||||||
| 13 | 270-555-1040 | |||||||||
| 14 | Eggleton Leo Accounting | Eggleton Leo Accounting | 1328 Magnolia St | Some City | KY | 42104 | 270-555-8903 | 270-555-1295 | ||
| 15 | 1328 Magnolia St Some City KY 42104 | |||||||||
| 16 | 270-555-8903 | |||||||||
| 17 | 270-555-1295 | |||||||||
| 18 | Gregory Hubert C CPA | Gregory Hubert C CPA | 4560 Lecta Kino Rd | Some City | KY | 42141 | 270-555-2229 | Fax: 270-555-5870 | ||
| 19 | 4560 Lecta Kino Rd Some City KY 42141 | |||||||||
| 20 | 270-555-2229 | |||||||||
| 21 | Fax: 270-555-5870 | |||||||||
| 22 | Childplace | Childplace | Some City | KY | 270-555-3077 | |||||
| 23 | Some City KY 270-555-3077 | |||||||||
Sheet1 | ||||||||||
