pasting data from HTML table

Originally Posted by bmccall17

I am attempting to import data from an html table. When I copy and paste I get EVERYTHING in a single column. And I cannot identify any uniformity to be able to even start to figure how to translate this.


Here are a few lines and the results I get:

Lot Neighborhood Acreage Lot Type Price
18 Southpoint 5.22 Lake/Single Pier $1,800,000
46 Southpoint 3.31 Lake/Single Pier $995,000
21 Southpoint 3.98 Lake/Shared Pier $850,000

When I paste, here is what I get:
Lot

Neighborhood

Acreage

Lot Type

Price
18
Southpoint
5.22

Lake/Single Pier

$1,800,000
46
Southpoint
3.31

Lake/Single Pier

$995,000
21
Southpoint
3.98

Lake/Shared Pier

$850,000


Please advise how I to get this into excel in a manageable format!


Thanks,
bam

Hi,

from the data displayed my thought would be to firstly paste in into Notepad, and then copy and paste from Notepad into Excel, to see if that 'straightens' the appearance.

If not, then, looking at the display, the headings are spread over 9 rows but the data over 7 rows.
delete two blank heading rows, the pair above and below Neighourhood, then, in C1 put

=INDIRECT("A"&(ROW()-1)*7+COLUMN()-2)

and formula fill this sideways to column I, then bulk-formula fill the selection downwards for as many rows as are required (one seventh of the data in column A.

To fix the data, (bulk) copy columns C to I and Paste Special = Values back over themselves

you can then delete column A (and the blank B)

hth
---

Originally Posted by bmccall17

Thank you... I didnot understand your formula, but it worked.

Would love a little more explaination about why and what this does.
I read over the help documentation.


If you are willing.
Thank you,
bam

Hi,

=INDIRECT("A"&(ROW()-1)*7+COLUMN()-2)


=INDIRECT( <<<< means, make up a formula from the following

"A"&(ROW()-1)*7+COLUMN()-2)

the letter A
the current Row() number minus one
times 7
plus the current column() number minus two

hh
---