Pulling numbers out of a cell

All replies

• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  1

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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