Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: numbers to text and text to numbers

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

    Default numbers to text and text to numbers

    Hi
    I just watched a video of excelisfun, the link below and he suggested to change text to numbers by using -- in front of a function Right().

    My question, why he did not suggest to go to Home-->Number--> then select number to convert to text to number.

    Another question. If I have the following numbers in a column (see below please A1:A3). If I change them from number to Text (Home-->number-->Text). They will be aligned to the left side of the cells but if I do for example C1 =A1 +10, I will get 20?
    Why is that when A1=10 is a text not a number

    10
    20
    30

    Thank you very much.

    https://www.youtube.com/watch?v=mjLvi0gu494

  2. #2
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,436
    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
    I just watched a video of excelisfun, the link below and he suggested to change text to numbers by using -- in front of a function Right().
    My question, why he did not suggest to go to Home-->Number--> then select number to convert to text to number.
    I don't know what Home > Number does in your version of Excel. In my version, you might be talking about the feature on the ribbon that selects the cell format. Note that it selects a format for displaying values. It does not convert text to numbers, nor numbers to text.

    To demonstrate, format A1 as Text, and enter 123. Then change the format of A1 to Number. If we enter =SUM(A1), it returns zero(!) because the type of the value in A1 is still text, despite the cell format.

    In order to change the type of the value, we must "re-enter" the value or formula. One way: select the cell, press the key f2 (not the cell F2), then press Enter. (Press ctrl+shift+Enter if the formula is an array formula.) Then =SUM(A1) returns 123.

    -----

    Besides, the video did not put the text into a cell; so the cell format is not relevant.

    The video demonstrates the use of =SUMPRODUCT(--RIGHT(A1:A10)), which always returns a number.

    The point of the video is: without double-negate ("--"), RIGHT returns text, even though A1:A10 contains numbers; and SUMPRODUCT ignores text in array or range parameter. Consequently, without double-negate, SUMPRODUCT returns zero.


    Quote Originally Posted by lezawang View Post
    Another question. If I have the following numbers in a column (see below please A1:A3). If I change them from number to Text (Home-->number-->Text). They will be aligned to the left side of the cells but if I do for example C1 =A1 +10, I will get 20?
    Why is that when A1=10 is a text not a number
    First, again, when you simply change the format from Number to Text, the type of the value is not changed; just its appearance. The type of value remains a number. So even =SUM(A1,10) would return 20.

    But if you convert the type of the value to text by "re-entering" (e.g. press f2, then Enter), =SUM(A1,10) returns 10 because SUM ignores text, namely the value in A1.

    On the other hand, =A1+10 would still return 20 for the reason explained in the video: any arithmetic operation converts text (that Excel can interpret as a number) to a number. In this case, the arithmetic operation is "+". So we do not have to write --A1.

    However, that applies only to arithmetic operations, not comparisons for example. So =A1=10 returns FALSE. In that case, we would need to apply an arithmetic operation to A1. Examples: =--A1=10 or =A1+0=10 or =A1*1=10 or =N(A1)=10.

  3. #3
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,659
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: numbers to text and text to numbers

    What you're doing is changing the format of the cell, not the underlying value.

    If the number was entered as text originally, then it will still be text, you need a conversion method such as the one in the video to change it into a valid number before the change of format will work.

    There are a number of ways to do it with formulas, or you can use text to columns from the Data tab if you want to do it formula free.

  4. #4
    Board Regular
    Join Date
    Oct 2011
    Posts
    4,258
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: numbers to text and text to numbers

    He had an array of text {"1","2","9"....}. Home - Number will not change this array to numbers. As he stated an math operation will change text to number (you could add 0 or multiply by 1).
    In answer to your second question. you applied a math operation which changed the text to numeric.

  5. #5
    Board Regular RasGhul's Avatar
    Join Date
    Jul 2016
    Posts
    548
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    2 Thread(s)

    Default Re: numbers to text and text to numbers

    I just did a test and numbers entered as text wouldn't change back to numbers with number format, but --right resulted in values numbers.

    + is a math operator like -- and converts text numbers to numbers

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

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

    Default Re: numbers to text and text to numbers

    Thank you all. Sorry I was not clear. Let me please explain it again. I have A1=10, A2=20, A3=30. I highlighted A1:A3, then click on Home Tab --> went to Number group. Clicked on General menu, Select Text.

    Now I have all these numbers moved to the left side of the cells. Now when I click on B1 and type =Sum(A1), I get 10.

    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? Thank you very much.

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

    Default Re: numbers to text and text to numbers

    Can you please tell me why we have this option:

    Home tab --> Number group --> click on general group --> Text

    Why this is needed? any real life example would appreciate to understand why I need to format as Text. Thank you all once again

  8. #8
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,812
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: numbers to text and text to numbers

    Imagine having the number 1234.00001 in a cell.
    IF you don't need to apply any mathematical equation to it, but simply want it to appear as text for the visual result, converting it to text will simply show..."1234"
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

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

    Default Re: numbers to text and text to numbers

    Thank you very much. I inserted 1234.00001 and then changed to Text, but nothing happened. The same number appear, only moved to the left.
    Then I clicked on an empty cell and changed the format to Text and then enter the number. The number this time moved to the left but the same number exactly 1234.00001 and this time the cell has green triangle

    Thank you very much

  10. #10
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,812
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: numbers to text and text to numbers

    What was it formatted as before you typed in 1234.0001 ??
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

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
  •