I have a program that ONLY creates an HTML table as an output (I have
no control over this and cannot reprogram it). The HTML table produces
5 columns, 4 of which are numeric but one of which contains numbers
but not with numeric values. These are in the form of 01.210 etc and
the zeroes are really rather important! I need to take two of these
output tables and compare them in Access, so have tried copy/paste and
copy/paste special into Excel as an intermediate stage. Copy/paste
retains the column separations but changes all 01.110 type values into
1.11 which is unacceptable. Copy/paste special retains the 01.110 type
values but throws everything into one column, which is also useless to
me...

How do I, without having to write code or buy additional software,
copy and paste the data from the table so that in Excel (of in Access
if it can be done directly) it retains both the 5 columns and the
01.110 type values?

P.S. It looks like this could be done in Access simply by importing
the HTML file but it goes through the import process then kicks it out
at the final step with the comment that the file isn't in the expected
format, which may be a bug in my version of Access but is not one that
can be fixed - the company don't allow patches etc.

Hello Wolvies. The simplest way to import the table into Excel is to
open the web page with Internet Explorer, right click on the table and
choose "export to Mitcorosft Excel". This does not work 100% of the
time, but usually will.

So you import it that way or using your own cut and paste method. Now
for the  problem. To retain the triple decimal place format, select
all of the cells necessary in Excel and pull down the "Format" menu at
the top and choose "Cells". Click on the "Number" tab and change the
"Decimal Places" from 2 to 3 and that should do it!

These instructions may vary slightly if you are using a Macintosh or
an older version of Excel. I don't have either a Mac or an older
version of Excel on hand, but if you have trouble with these
directions use the "ask for clarification" feature and I will help you
out. I'll be looking on here through the day.

Good luck with your task!
-shiva777

---

Clarification of Answer by shiva777-ga on 08 May 2003 12:15 PDT

Wolvies I'm really sorry I missed that! That will teach me to answer
questions so early in the morning. :-S

I wanted to add one more thing that might be helpful. 
Another alternative to changing it to "text" is to use the "special"
option
when you format the cell and in this case you could put 00.000 for the
format.
This way would allow you to still do numerical calculations if that is
necessary.

good luck!
-shiva777

Thank you to Shiva and to Reeteshv (by the time I read both I was able
to get it to work, so apologies to later commentators - I didn't need
to try any other version). Shiva, the 3 decimal points works to put a
trailing zero on but didn't solve the leading zero problem. Reeteshv
'Text to Column' is a very useful function to know - thank you very
much :)

Dear wolvies-ga,

Good day!

Adding to Shiva777's answer, should you need to preserve the
formatting of the type 01.110 (i.e., both the leading & the lagging
zeroes), you can follow this procedure:

1. Select the values to be copied
2. In Excel, use the "Paste Special" command to paste the values in
one cell
3. Select the "Text to Column" option under the "Data" menu
4. Follow the instructions given for Step 1 and Step 2
5. In Step 3, select the last column and choose "Text" as the coulmn
data format
6. Click "Finish"

The above procedure will keep 01.110 as 01.110! I tried to compare the
values in the last column and did not notice any weird behaviour!! You
can also write a macro to automate this entire task.

Hope this helps :)

Regards,
reeteshv-ga

You can do a regular copy and paste.  But first, click on the letter
of the column which is not numeric.  Right click and choose Format
Cells.  Under the number tab, select Text.