Text analysis using Excel?

[David Morrison]

Yes, I know it sounds a strange request, but it's not as strange as it
may seem.

Basically, I need to grab some info from a web page to use as data in a
spreadsheet. To date, I have just typed in the values, and that
certainly worked.

But it dawned on me that maybe I could copy the web page and paste it
into a blank worksheet, then extract the bits I want from that. Maybe
some time in the future, I could even do the copy and paste by
AppleScript.

Anyway, it turns out that when you paste a web page into a worksheet,
the different parts fill different cells. Theoretically therefore, it
should be easy to grab what I want. And it would be if the web page
layout was fixed.

Unfortunately, there are bits that sometimes appear on the page and
sometimes not. This causes one of the bits of information I want to
sometimes appear in cell C18, but equally likely to appear anywhere in
c18:c25 depending on what else has appeared on the web page before it.

So I am wondering if there is some way to identify which cell a
particular piece of data is in based on what else is in the worksheet.

Here is an example. Say that on a page there is a heading "Production",
and immediately under it is a number that I want to use. When the page
is pasted into Excel, the number is in the cell immediately under the
heading.

Now, the web page may contain a variable number of rows before those
lines, so it may be in C18 or any of the next 6 cells below it.

Is there some way to identify the cell I am after so it can be used on
another worksheet?

The only way I can think is to use nested if statements like this:

=if(c18="Production",c19,if(c19="Production",c20,if(c20="Production",c21,
if(c21="Production",c22,...))))

Is there a better way?

Thanks

David

 

[John Halloran]

Yes, I know it sounds a strange request, but it's not as strange as it
may seem.

Basically, I need to grab some info from a web page to use as data in a
spreadsheet. To date, I have just typed in the values, and that
certainly worked.

But it dawned on me that maybe I could copy the web page and paste it
into a blank worksheet, then extract the bits I want from that. Maybe
some time in the future, I could even do the copy and paste by
AppleScript.

I suggest copying only the data you want from the web page, and pasting
it into a text editor that can save the data as a plain text file.

Open the plain text file in Excel, and Excel will make a determination
that file contains either delimited or fixed width data. If the data is
organized in a consistent manner, one of those two file types should
display the data grouped as you want in Excel.

John

 

[David Morrison]

I suggest copying only the data you want from the web page, and pasting
it into a text editor that can save the data as a plain text file.

Open the plain text file in Excel, and Excel will make a determination
that file contains either delimited or fixed width data. If the data is
organized in a consistent manner, one of those two file types should
display the data grouped as you want in Excel.

Thanks for the suggestion. When you copy a web page, all you get is the
text, seemingly with field delimiters which Excel recognises as it puts
it all nicely into columns and rows.

Unfortunately, the data I want is scattered all over the page, so just
copying multiple parts of it is likely to be as much work as typing it
in.

Cheers

DAvid