External data table is imported as text and cant be changed to number

[Bondator]

I am trying to create a pivot table from an external data file. The data file is an .xls file with whole bunch of numerical laboratory results in it. The thing is, not every product is tested for everything, so it's natural that some fields are left blank.

I've figured out that the problem happens when the first row of the data happens to be blank, then the entire column is imported as text, not as number. And of course text values don't work at all in a pivot table. Trying to change the column number format in the imported table doesn't seem to do anything either.

Any ideas on how to force a number format on the imported data table?

 

[Chrisdontm]

Something to work with;

If a number that is text is multiplied by something it turns it back into a number to work with.

If you could always download it into another tab and then set another tab to multiply whatever gets downloaded.

Perhaps you can build it from there...

Just a thought...

 

[Bondator]

Thanks for the thought. Creating a new column and using formula below seemed to work.

=IF(NOT(ISBLANK(source);VALUE(source);"")

Weirdly though, as I'm doing this exactly the same thing today with fresh data, the values are imported just fine as numbers, even with the first cell empty. Now I have no idea what Excel's logic is with this thing.