locked
Pulling numbers out of a cell RRS feed

  • Question

  • I have a 9 digit number and want to pull out the last 5 and return that value as a number.  I know that there is a text function to do this, but I need the result to be a number.

    Thanks for the assist...

    Sunday, June 5, 2011 4:52 PM

Answers

  • But try
    =sum(b1)
    or
    =sum(b:b)

    Do those text values appear in the sum?

    When you do arithmetic on text values, excel will try its best to treat
    something that looks like a number as a number -- but not all functions will
    behave the way you want!

    And the -- stuff is just a couple of minus signs. 

    The first one coerces the text string into a number (but with an opposite
    sign).  The second minus changes the sign back.

    You could accomplish the same effect by:
    =right(a1,5)+0
    or
    =right(a1,5)*1

    renatoabc wrote:


    Hi Ron de Bruin;

    Acctuallly, if you check the result of =Right(A1;2) with =isnumer(B1), the result will be False, in a way one maybe think you can not use B1 in formulas.

    but when you have A1=123456789 , B1=right(A1;2) and C1=B1+1, the result in C1 is 90.

    This way you can use the resulting number in calculation, they maybe behave diferent in Excel 2007, but in Excel 2010, for sure you can.

    But I have a question, if you would be so kind in answering it, what is the purpose of the two -- before your initial fórmula ( B1=--Right(A1;5)?

    I'd love to know.



    Regards,

    Renatoabc






    --

    Dave Peterson

    • Proposed as answer by renatoabc Monday, June 6, 2011 3:46 PM
    • Marked as answer by William Zhou CHN Monday, June 20, 2011 6:39 AM
    Monday, June 6, 2011 10:56 AM

All replies

  • Use
     
    =--RIGHT(A1,5)
     
     
     

    Regards Ron de Bruin
    http://www.rondebruin.nl/tips.htm


    "Tom92551" wrote in message news:64f5968f-23a2-4c07-b224-e039de94bb36...

    I have a 9 digit number and want to pull out the last 5 and return that value as a number.  I know that there is a text function to do this, but I need the result to be a number.

    Thanks for the assist...

    Sunday, June 5, 2011 5:00 PM
  • Use
     
    =--RIGHT(A1,5)
     
     
     

    Regards Ron de Bruin
    http://www.rondebruin.nl/tips.htm


    "Tom92551" wrote in message news:64f5968f-23a2-4c07-b224-e039de94bb36...

    I have a 9 digit number and want to pull out the last 5 and return that value as a number.  I know that there is a text function to do this, but I need the result to be a number.

    Thanks for the assist...

    Maybe just a spelling mistake,

     

    Won't be enought to have =Right(A1,5), I tryed this way and worked as a number

    Renatoabc 


    Sunday, June 5, 2011 9:57 PM
  • It depend what you do with it
     
    =Right(A1,5) is not a number, try =ISNUMBER(B1) to test
     
     
     
     
     
     
     

    Regards Ron de Bruin
    http://www.rondebruin.nl/tips.htm


    "renatoabc" wrote in message news:f3919435-8113-4b12-8452-a1b26112da58...
    Use
     
    =--RIGHT(A1,5)
     
     
     

    Regards Ron de Bruin
    http://www.rondebruin.nl/tips.htm


    "Tom92551" wrote in message news:64f5968f-23a2-4c07-b224-e039de94bb36...

    I have a 9 digit number and want to pull out the last 5 and return that value as a number.  I know that there is a text function to do this, but I need the result to be a number.

    Thanks for the assist...

    Maybe just a spelling mistake,

     

    Won't be enought to have =Right(A1,5), I tryed this way and worked as a number

    Renatoabc


    Monday, June 6, 2011 3:11 AM
  • Bonsour®
     
    "Tom92551" a écrit dans le message de groupe de discussion :
    64f5968f-23a2-4c07-b224-e039de94bb36@communitybridge.codeplex.com...
    > I have a 9 digit number and want to pull out the last 5 and return that value
    > as a number.  I know that there is a text function to do this, but I need the
    > result to be a number.
     
    =INT(A1/10^5)
     
     

    Maude Este
    Monday, June 6, 2011 8:53 AM
  • Hi Ron de Bruin;

    Acctuallly, if you check the result of =Right(A1;2) with =isnumer(B1), the result will be False, in a way one maybe think you can not use B1 in formulas.

    but when you have A1=123456789 , B1=right(A1;2) and C1=B1+1, the result in C1 is 90.

    This way you can use the resulting number in calculation, they maybe behave diferent in Excel 2007, but in Excel 2010, for sure you can.

    But I have a question, if you would be so kind in answering it, what is the purpose of the two -- before your initial fórmula ( B1=--Right(A1;5)?

    I'd love to know.

     

    Regards,

    Renatoabc

     

     

     

     

    Monday, June 6, 2011 9:28 AM
  • But try
    =sum(b1)
    or
    =sum(b:b)

    Do those text values appear in the sum?

    When you do arithmetic on text values, excel will try its best to treat
    something that looks like a number as a number -- but not all functions will
    behave the way you want!

    And the -- stuff is just a couple of minus signs. 

    The first one coerces the text string into a number (but with an opposite
    sign).  The second minus changes the sign back.

    You could accomplish the same effect by:
    =right(a1,5)+0
    or
    =right(a1,5)*1

    renatoabc wrote:


    Hi Ron de Bruin;

    Acctuallly, if you check the result of =Right(A1;2) with =isnumer(B1), the result will be False, in a way one maybe think you can not use B1 in formulas.

    but when you have A1=123456789 , B1=right(A1;2) and C1=B1+1, the result in C1 is 90.

    This way you can use the resulting number in calculation, they maybe behave diferent in Excel 2007, but in Excel 2010, for sure you can.

    But I have a question, if you would be so kind in answering it, what is the purpose of the two -- before your initial fórmula ( B1=--Right(A1;5)?

    I'd love to know.



    Regards,

    Renatoabc






    --

    Dave Peterson

    • Proposed as answer by renatoabc Monday, June 6, 2011 3:46 PM
    • Marked as answer by William Zhou CHN Monday, June 20, 2011 6:39 AM
    Monday, June 6, 2011 10:56 AM
  • Thanks a lot Dave Peterson,

    It is the answer I was looking for.

    the double -- is a really nice trick.

     

    Renatoabc

    Monday, June 6, 2011 3:48 PM
  • Hi renatoabc
     
    Yes it will work then but not for example when you select the cells and look at the sum in the statusbar
     
    Dave already answer the --
    Great that other people answer when I sleep and work<g>
     
     
     
     

    Regards Ron de Bruin
    http://www.rondebruin.nl/tips.htm


    "renatoabc" wrote in message news:cd3155a6-cb8b-4dbd-a98a-701da28925b8...

    Hi Ron de Bruin;

    Acctuallly, if you check the result of =Right(A1;2) with =isnumer(B1), the result will be False, in a way one maybe think you can not use B1 in formulas.

    but when you have A1=123456789 , B1=right(A1;2) and C1=B1+1, the result in C1 is 90.

    This way you can use the resulting number in calculation, they maybe behave diferent in Excel 2007, but in Excel 2010, for sure you can.

    But I have a question, if you would be so kind in answering it, what is the purpose of the two -- before your initial fórmula ( B1=--Right(A1;5)?

    I'd love to know.

     

    Regards,

    Renatoabc

     

     

     

     

    Monday, June 6, 2011 4:18 PM