formulas not returning value

[tinkythomas]

Hi,

I have a workbook with several worksheets and considerable number of formulas, one worksheet has over 30,000 rows.

The problem is the formula isn't returning a value but the formula itself. Now I know this is due to data types, I can format the cell as general then hit F2, enter and the value is returned.

The problem is if I copy the formula down the result from the first cell is copied to all cells. I understandably don't want to format, F2 and enter on every cell!

Thanks

 

[Jon von der Heyden]

Hello Chris

Highlight the column that contains the formulae, and format the whole column as 'General'.

With the column selected on the menu bar go Data > Text to columns > choose 'Delimited' and hit 'Finish'

 

[VoG]

Try this:

Select all the cells.

Set format to General.

Edit > Replace, replace = with =

 

[tinkythomas]

Thanks for the replies, unfortunately both methods copied the first cells value to all other cells.

Any more ideas, thanks for your help so far.

N.B I did try forcing excel to calculate shift+F9 but this didn't help!

 

[tinkythomas]

Ok I finally solved this. Select the first cell, format as general then F2 and enter, copy formula down. This gives the same result as before but now when forcing re-calculate (shift+F9) the correct values are returned.

I would like to write a vba procedure to do this as part of a SAP data import routine. I'm having this problem every time data is imported from SAP (on a weekly basis).

Thanks for your help!

 

[Alicitag]

OMG!!! This helped a lot. Thanks guys