Number format force 2 digits

[Dharris144]

Hello everyone,
I hope I have an easy problem.
I copy data from Navision our ERP software to Excel. I then remove all duplicate entries. I am having a problem due to number formats.
Some of the data is 2 decimals and some is not. So when I copy two lines I get

Apples 1622
Apples 1622.00

Excel does not see this a duplicate entry so I am guessing I need to force the data to have two decimals.

Any help would be deeply appreciated

 

[Special-K99]

Presumably this is text otherwise Excel would see 1622 and 1622.00 as the same.

Assuming A1 is the number

=IF(RIGHT(A1,3)<>".00",A1&".00",A1)

will result in all numbers ending in ".00"

 

[Zakkaroo]

I think it depends what you mean. Imagine if your data is in column A

Is it:
A1 = Apples 1622
A2 = Apples 1622.00

or is it:

A1 = Apples
B1 = 1622

A2 = Apples
B2 = 1622.00

If it's the first scenario and all cell values are similar in layout to what you've outlined above, then you could do:

B1 =IFERROR(LEFT(A1,FIND(".",A1)-1),A1)

That would make B1 = Apples 1622 and B2 = Apples 1622

You could then succesfully remove duplicates.

This depends a lot on what your data actually looks like and what other variations are there.

If it's the 2nd scenario, you would need to just format the whole column as numbers with either 0 or 2 decimal places, as I believe excel sees '1622' formatted as general, to be different to 1622.00 formated as number to 2 decimal points.