Excel doesnt save currency with trailing Zeros

Hi All

A user in the company I work for raised a strange query with excel that I haven't been able to work out. When she saves an ammount such as £1.00, excel saves this only as a 1

I realise that this is the same number but when she searches for 1.00 excel doesnt find it, the spreadsheet has thousands of rows and is the same for 1.40 (saved as 1.4).

Not sure if there is a way round this toi make it easier for my user other than having her search without the zero which is likely to be forgotten.

Hope this makes sense and thanks in advance

Chris

Hi Tony,

Thanks for the quick reply. Yeah it displays the number fine but when you look at the value the trailling zeros are gone. I know I could change it to text and force it but the calculations don't work correctly. It's excel 2003 that the user is on but it also happens on my Excel 2010.

Thanks

Chris

Hi Chris

As I explained it is because the zeros have no value so to store trailing zeros would simply take up disk space where there is no benefit in doing so.
Can you explain what the actual problem is you are having so I can try and help you further.
Tony

Hi Tony,

The issue is when our accounts people search for amounts they search for them as displayed on an invoice. so 1.40, 2.00 and they aren't the most computer literate of people to remember not to search for 1.40 but 1.4 instead. . Was hoping to find a way so this would work but I understand the reason behind it not saving the zeros.

Thanks again for your response

Chris

Hi Chris
:-) Now I understand your request.
Can they not search for an Invoice Number and then validate the amounts?
Tony

Hi Tony

I will look into how they use this to see if they can do it another way, like I said it's not something I've come across before, so potentially the way one person uses the spreadsheet compared to another. But would be great to find a way round this.

Chris

Could it not be easier to use an AUTOFILTER from Data->Filter->Autofilter ???

Originally Posted by FDibbins

interesting.....

This is the same as we are facing, even though 1.00 is the same as 1, excel doesnt find them.

Will give that a go, it could be a useful way round it.