Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: numbers to text and text to numbers

  1. #11
    Board Regular
    Join Date
    Mar 2016
    Posts
    1,385
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: numbers to text and text to numbers

    First, it was general, then I typed the number, then went to Home-Number->Text. It just moved to the left, the whole number.
    Then selected another cell, changed the format to Text, then enter the number, again the whole number appears to the left with green triangle in the cell. Thank you

  2. #12
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,430
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: numbers to text and text to numbers

    Quote Originally Posted by Michael M View Post
    Imagine having the number 1234.00001 in a cell. [....] converting it to text will simply show..."1234"
    But simply changing a numeric format (General, Number, etc) to Text, as Leza is doing, does not "convert it to text".

    And the appearance after changing the format to Text depends on how the number was displayed initially.

    If we enter 1234.00001 into a cell with default width and formatted as General, yes, Excel displays 1234. Then when we set the format to Text, it continues to display just 1234, but on the left because that is the default horizontal format for Text cells.

    Moreover, if we enter 1234.00001 into a cell with numeric format and make the cell wide enough to display 1234.00001, then when we set the format to Text, it continues to display 1234.00001 unless and until we make the cell too narrow.

    But look at the Formula Bar. The value is still 1234.00001. Make the cell wide enough, and Excel displays 1234.00001, even when formatted as Text.

    The fact is: ISNUMBER(A1) returns TRUE, and ISTEXT(A1) returns FALSE. So the type of the value is still numeric.

  3. #13
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,430
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: numbers to text and text to numbers

    Quote Originally Posted by lezawang View Post
    That is what I do not understand. A1 now is a text which looks like 10. Why when I do sum(A1), I get 10? So what is the point of converting it to Text when is still can be used as a number?
    I explained all this in response #2 , posted 20 minutes earlier.

    First, you are not "converting" to text when you change the format to Text. Proof: ISNUMBER(A1) is TRUE after changing the format to Text.

    The appearances (on the left) and the cell format (Text) do not affect the type of the value (numeric).

    Is that clear?

    That should explain why SUM(A1) returns a numeric value (10) instead of zero. The cell value is numeric. Again, ISNUMBER(A1) is TRUE.

    Is that clear now?

    ----

    It is also true that if a cell is formatted as Text and we enter "a number" (numeric text), it will continue to be treated as text after we change the format to General, Number or some other numeric format.

    "What is the point?" Sometimes there is no "good" reason. It is simply the way things work in Excel.

    Is that clear?

    If I had a $1 for every "odd" or "dumb" thing that Excel and other Office products do, I'd be as rich as Bill Gates, too. (wink)

  4. #14
    Board Regular
    Join Date
    Mar 2016
    Posts
    1,385
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: numbers to text and text to numbers

    Yes it is very clear. Thank you so much all.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •