0

I have been trying to copy data into excel and format it into a table, as it needs to be imported into a system. The only issue is is that it's formatted web data.

On import the file is completly jumbled, even with dilimiters. I'm not all that excel savy, does anyone know how I can turn this list of addresses into a table I.e.

   Deliver To | Street / RD | City / Town | County | Postal | Tel | Fax | Email
1
2
3
4
5
Lewis
  • 1,945
  • 5
  • 26
  • 52

1 Answers1

1

If you right-click on the page and select View Source, each item is listed pretty cleanly in the code. I put all your fields in CAPS.

                <li>
                    <h2>CITY</h2>
                    <div class="acc-section">

                        <div class="acc-content">
                            <p>STREET, DELIVER, CITY, COUNTY. POSTAL</p>
                            <p>T: <strong>TEL</strong><br /> F: <strong>FAX</strong><br /> E: <a href="mailto:EMAIL">EMAIL</a>
                            </p>
                        </div>

                    </div>
                </li>

Take a bit of time to strip out the markup with some find/replace, or write a fun regular expression to do it. Once you get it down to a list like this:

STREET
DELIVER
CITY
COUNTY
POSTAL
TEL
FAX
EMAIL

You can then write an Excel formula that transposes the data to the format you want ... something that takes the second cell, and then every eighth cell after that for your first column, for example.

Once you have all the data formatted how you want, copy it, and paste as value, to remove the Excel formulas.

But, that's assuming you want a table in Excel. Your title suggests what you really want is a CSV. Once you have the list, it'd be much easier to create a text file with comma separated values (CSV):

STREET,DELIVER,CITY,COUNTY,POSTAL,TEL,FAX,EMAIL,742 Evergreen Terrace,Mr. and Mrs. Homer J. Simpson,Springfield,NT,49007,555-6832,,chunkylover53@aol.com, ....
undrline - Reinstate Monica
  • 526
  • 1
  • 7
  • 18
  • I have stripped all the markup, and have dilimitered all the information with commas. Im a complete noob on excel formulas. Is there some refrence material you might know that would be appropriate in this situation. – Lewis Jul 26 '16 at 15:02
  • Your edit, regarding the CSV and comma seperation is where I'm at. It's the next stage I'm struggling with. Importing the data just jumbles it all. :/ – Lewis Jul 26 '16 at 15:12
  • 1
    @Beaniie you commented while I was editing (I spent too much time looking up the actual values for the Simpsons). Excel will open an import wizard when you try to open a CSV in Excel. Assuming you have your headers as I've put them, and you follow the steps in the wizard appropriately, Excel will have that as the top row. You just need to reorder the columns. – undrline - Reinstate Monica Jul 26 '16 at 15:14
  • 1
    @Beaniie, it's possible you have extra commas, or you don't have empty fields delimited properly (see the missing fax number in my example). – undrline - Reinstate Monica Jul 26 '16 at 15:15
  • MY GOD! Missing data! -.- * Facepalm. I had no Idea... That would explain it. – Lewis Jul 26 '16 at 15:18
  • @Beaniie :) happens to the best of us. – undrline - Reinstate Monica Jul 26 '16 at 15:20
  • Thanks again, you absolute legend! :) – Lewis Jul 26 '16 at 15:20
  • Just a tag on question some of the data in the list has a Unit reference I.e `Unit 9` that would need it's own heading wouldn't it? – Lewis Jul 26 '16 at 15:29
  • 1
    Glad I could help. Thanks for marking as answered; put me back over the edge for "comment anywhere." – undrline - Reinstate Monica Jul 26 '16 at 15:29
  • Glad I could be of assistance! :) – Lewis Jul 26 '16 at 15:29
  • 1
    @Beaniie Not sure about the question. If you have `123 Fake Street, Unit 9` I'd just get rid of the extra comma. If you need to have the comma, you can escape the entire field with double quotes (http://stackoverflow.com/a/769675/1861282). There are also ways of replacing it with a newline character. – undrline - Reinstate Monica Jul 26 '16 at 15:32
  • Understood I'll probably just ditch the comma. Thanks again! :) – Lewis Jul 26 '16 at 15:42