Forum Discussion

Copper Contributor

Apr 25, 2018

Cell formatting

In an Excel worksheet a selected the "number" format with 2 decimals for all cells. Many cells show numbers with that formatting all right but other keep showing them with 13 decimals. I don't see w...

excel

Haytham Amairah

Silver Contributor

Apr 25, 2018

Hi Pierre,

It seems that these numbers are treated as texts, not real numbers!

Please try this trick:

Select the numbers
Go to Data >> Data Tools >> Text to Columns
In Text to Columns window, click Finish

Hope that helps

Haytham

Pierre RUEL

Copper Contributor

Apr 25, 2018

Thank you Haytham,

So did I, but my data wer'n't a text and I got the integer part of my number in one column, i.e. 20,00, and the whole series of decimals in the second one.

But my original number was 20,7524886114392 and with two decimals it should show 20,75 and not 20,00. Sorry but it didn't work.

Another idea ?

Regards

Pierre

Haytham Amairah
Silver Contributor
Apr 25, 2018
Hi Pierre,

If so, please try to play more with the same tool Text to Column as follows:
Select the column of numbers
Go to Data >> Data Tools >> Text to Columns
In Text to Columns window, Choose Fixed width and click Next twice
In the last step, click the Advanced button
In the Advanced dialog box, check if the decimal separator is the comma (,) and the thousands separator is the dot (.)
Then click OK and Finish

Hope the helps