Answered by:
Pulling numbers out of a cell
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)*1renatoabc 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:64f5968f23a24c07b224e039de94bb36...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:64f5968f23a24c07b224e039de94bb36...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:f391943581134b128452a1b26112da58...Use=RIGHT(A1,5)
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
"Tom92551" wrote in message news:64f5968f23a24c07b224e039de94bb36...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 :64f5968f23a24c07b224e039de94bb36@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 EsteMonday, 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)*1renatoabc 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 renatoabcYes it will work then but not for example when you select the cells and look at the sum in the statusbarDave 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:cd3155a6cb8b4dbda98a701da28925b8...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