Organising an address list in Excel

  VNAM75 15:35 28 Feb 06
Locked

I have a list of company names:

click here

which I have pasted into Excel and I need to arrange it into 3 columns in the following format:

Company name Address Postcode

What is the fastest way of doing this? I have another 20 of these pages and going through them individuallly will take me ages.

  GroupFC 15:54 28 Feb 06

I am not sure there is an easy way!

Presumably you just copied and pasted the details and therefore have the first co's name and address etc. in the first cell and then the next co. in the next cell and so on down the column.

I can't think of any easy way to extract the details you want into columns!

  Simsy 16:15 28 Feb 06

but if you look at the HTML for the page, (View source), you might be able to do something with the text "searchlink"...

This appears in the HTML code before each company name... I'm sure some clever use of the various "find" functions could be incorporated into a macro, (by someone far better than I!!), to get somewhere near what you seek.

Good luck,

Regards,

Simsy

  VNAM75 16:19 28 Feb 06

Thanks, hope someone can do something with the html code. It makes no sense to me!

  VoG II 17:15 28 Feb 06

If you select an entry like

Headland Multimedia, 11 Edison Village, Nottingham Science Park, Nottingham, Nottinghamshire, NG7 2RF, UK

Data|Text to columns, select Delimited with Comma as the separator, this will produce Columns A:G containing the company name and address. You can then merge B:E if you wish.

You can actually select multiple rows and do the above.

  Simsy 20:48 28 Feb 06

VoG™'s method is the way to go...

I was assuming, without checking that you were copying the whole page, and that it would appear as complete rubbish when pasted into Excel...

Having checked, it's not that bad, so just follow VoG™'s advice!

(Mind you.. I'm quite pleased with my lateral thinking on this one!)

Regards,

Simsy

  VNAM75 21:01 28 Feb 06

Thanks VoG™, thats brilliant. But excel wont allow me to select multiple rows.

  VoG II 21:06 28 Feb 06

That's odd. Excel (2003) allows me to do that - I checked on a sheet populated with your sample data before saying that it was possible. Make sure that you are just selecting rows in column A, assuming that is where the 'raw' addresses are.

  VNAM75 21:16 28 Feb 06

Sorry VoG™, just tried it again and it works. Thanks again.

This thread is now locked and can not be replied to.

What is ransomware and how do I protect my PC from WannaCry?

Disney layout supervisor Rob Dressel on the challenges of visualising Moana

Siri vs Google Assistant