# 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

• 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 Monday, June 6, 2011 3:46 PM
• Marked as answer by 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 Monday, June 6, 2011 3:46 PM
• Marked as answer by 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

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