In Excel, I have a cell value as number format, say 10/2. It displays as 10/2 but I would like 5.00.
It evaluates to 5.00 if I add an = before it i.e, =10/2, but I don't want to have to edit around 500 rows of that column.
I tried to use VALUE function but it evaluates it to 42279.00:
Cell B1 10/2
Cell B2 42279.00
Another data example is: 100/20.
Can you please tell me what has gone wrong, or is there any approach to get the cell value 10/2 to be evaluated to 5.00?
Please try applying a formula of the following kind to all your data:
=MONTH(B1)/DAY(B1)
Having done so you may select the results, Copy and Paste Special over their source (assumed to be ColumnB) and then delete the formulae.
Excel has, trying to be helpful, interpreted your entries as dates - the above should reverse the coercion.
Since now it seems not all entries have been coerced into dates, I suggest for those that have not:
=LEFT(B1,FIND("/",B1)-1)/MID(B1,FIND("/",B1)+1,LEN(B1))