Incorrect Raw Data Format

  • Thread starter Thread starter andyj
  • Start date Start date
A

andyj

Hopefully someone can help me with this problem !!

I use a custom made programme to extra data from a database into an
Excel file, on a monthly basis.

I am then using this data file to drive other spreadsheets, via links
and lookup tables.

Unfortunatley the data within data file produced by the custom
programme, is not recognised by the formulas in the other
spreadsheets.

I have found that if I edit the data file, and go to each cell and
press F2 and then Enter, and then re-save the file, the links then
work.

I cannot alter the format which the custom programme saves the data.

Is there anyway in which I can automatically change the format of the
data within the data file without having to manually enter every cell
(3,000 rows x 10 columns !!)

Any help will be much appreciated.

Andy
 
your problem is that the raw data has been entered as text. Probably the
easiest way is to take the value of each cell and then copy, paste
special>values. Because you have so many cells it would be better to
set up the following.

Assuming your 10 columns are consequetive i.e A thro' J and your rows
are 1 thro' 3000

in the first vacant column in row 1 enter =VALUE(A1), copy this to the
next 9 columns, and then copy these 10 cells to the next 2999 rows.
Finally highlight all of these new cells and copy and paste
special>values to A1. now all your cell in A1:J3000 will be numbers and
excel will recognise them as such.

Hope this helps

Mike
 
andyj said:
Hopefully someone can help me with this problem !!

I use a custom made programme to extra data from a database into an
Excel file, on a monthly basis.

I am then using this data file to drive other spreadsheets, via links
and lookup tables.

Unfortunatley the data within data file produced by the custom
programme, is not recognised by the formulas in the other
spreadsheets.

I have found that if I edit the data file, and go to each cell and
press F2 and then Enter, and then re-save the file, the links then
work.

I cannot alter the format which the custom programme saves the data.

Is there anyway in which I can automatically change the format of the
data within the data file without having to manually enter every cell
(3,000 rows x 10 columns !!)

Any help will be much appreciated.

Andy
Click to expand...

It's probably come in as text rather than numbers.
Copy a blank cell (NOT one formatted as text). Select your data area
(3000x10) and use
Edit > Paste Special > Add.
That should convert all text to numbers.
Does it then work?
 
Back
Top