Search code examples
excelcellnumber-formatting

Cell displays wrong number value

A1: value displays as 49.0 in cell but when clicked top bar displays 48.9847219431886.
A2: value displays as 43.0 in cell but when clicked top bar displays 42.9792446979968.

So If I want to use this cell value in another cell, let's say in CONCATENATE function, it concatenates wrong value.

=Concat(A1," - " ,A2) gives result 48.9847219431886 - 42.9792446979968

but I want it should be 49.0 - 43.0

I have checked the number format and it is set to 'Custom' with type _(* #,##0.0_);_(* (#,##0.0);_(* "-"??_);_(@_). Tried changing format to text and number but no luck.

Solution

  • You can force the formatting of each number being concatenated by writing

    =CONCATENATE(TEXT(A1, "#,##0.0"), " - ", TEXT(A2, "#,##0.0")).