Request A Free Quote Here
Feb
21
What is an automated way to convert a list of business mailing adresses into Excel with formatted columns?
By
cs asked:
Imagine copying a list of mailing addresses from a website, and pasting them into Excel. You get one column of data.
I need separate columns for Business Name, Address, Phone, Email, Website, etc.
Please answer if you know of free software to do this, or can explain how to do this with Excel, or with maybe a script or MS Access…
Thank you!!!
Depends what is the file suffix you want to convert. I’d recommend you go into ms Explorer and highlight the file. Eg if it has a csv suffix, right click and choose open with ms Excel. Excel help will give you assitance for the ‘parsing’ job you want to accomplish
Are these addresses in a uniform format (i.e. does each one have the same number of address lines, an email, a zip/post code, a phone number, etc in their address)? Is each address in a single cell?
If the answers to those is yes, then you may be able to use ‘Text to Columns.’
Highlight your column, then click on Data/Text to Columns and choose the Delimited option. Make a selection by ticking the box that separates the fields (comma?) and then finish the process.
If the answer is a no, then you probably won’t be able to do this automatically. Excel needs a common denominator in its data in order to be able to split and arrange text. I’ve done this exercise before and had to do a lot of juggling with sorting, filtering and writing vba code. Even then it needed human intervention and a lot of tidying up.
Good luck, I don’t envy your job.
First you going to have to get the data into a readable format for Excel to be able to import it.
How are you ‘copying’ the data from the website? Highlight and Ctrl-C? If so then you would want to Save/Paste that data to MSWord or wordpad, but it has to be a ‘Special Paste’ so that you can select the ‘Plain Text’ option.
That should get you a text document formatted as 1 line per entry.
You will then SAVE that text file as plain text or a TXT file.
To Import the Text to Excel I suggest to goto the Microsoft website and check out the FREE MS Office Training courses.
Here is the link to the tutorial about importing text to Excel. Enjoy.