Forum Discussion

Pierre RUEL's avatar
Pierre RUEL
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 why, can you explain ?

Thank you in advance.

Regards

Pierre

excel

3 Replies

Sort By
  • Haytham Amairah's avatar
    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's avatar
      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's avatar
        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

Resources