Numbers as text dropping trailing decimals

**Uncle.Bungle** · 10-02-2010, 08:54 AM

Using Excel 2007.

I have an HTML table that I've linked as a data source. The table contains software names and versions. When I refresh the data, the version 1.00 becomes 1. I've tried forcing the column to text, and even created a new template where the cells were text instead of general but the trailing digits still get truncated.

Does anyone know of some way to prevent it from treating this column as numbers during the import?

Thanks

**Marcol** · 10-02-2010, 09:00 AM

I take it you have tried Forrmat Cells > Number Decimal Places:= 2

If this is not your problem then post a sample sheet showing your table layout, and data as imported.

Cheers

**Uncle.Bungle** · 10-02-2010, 09:28 AM

The HTML lives in my intranet. Screen shots and sample sheet attached.

**Marcol** · 10-02-2010, 09:59 AM

Do you need the Version Number exactly as shown in the .jpg?
i.e. if there are trailing zeros, then the exact number of zeros have to be shown and if there are none then show none.

**Uncle.Bungle** · 10-02-2010, 10:10 AM

Yes, sorry, I probably should have been clear there, I do need it to match exactly. I sent a subset showing adobe since there were a lot of examples, but the overall list is more than 500. Sometimes it x.0, sometimes it's x.00, somtimes it's x.12.1234.1000.1. I found one MS article which told me to try putting an apostrophe infront of the value, but that HTML table is used as a data source for some in house apps as well so I can't do that.

Right now I'm using Access, but it doesn't have a way to import from a URL (only static HTML file) which has made things a bit more complicated.

I think I might be stuck, but if you have any suggestions, I'm open to them.

**Marcol** · 10-02-2010, 11:41 AM

Hmm?

I can't see a way to do this in one step, Excel always seems to trim/suppress leading/traling zeros in any cell it recognizes as a number.

I tried importing a txt file with all version numbers concatenated with a hash eg 2.0#, 1.00#, 10.2.00#, etc

Then with the sheet formatted as text, in the first free column

=LEFT(C1,LEN(C1)-1)
Drag/Fill down

This then returned the desired result, then Copy > Paste Special > Values and all is still okay

This could all be done programmatically, but there must be an easier way.

Anybody?

If you could provide a link to the web page you are using for your import, or save it as a text file (post it in word format, or zipped) , we might be able to take it from there if all else fails

I have mocked up a rough example in the attached file