Converting text to numbers with a twist
 

In Excel 2007, I have entries of the form: 500A+12. I Would like to get rid
of the A and sum the numbers (i.e. return 512). I can return 500 and 12
using the left, right, and len functions, but I'm assuming they are being
returned as text because when I sum them I get zero. Is there another way to
do this? If there is a formula, please also tell me what it would look like
if there were spaces involved (e.g. 500A + 12). Thanks

Converting text to numbers with a twist
 

LEFT and RIGHT do indeed return text values, but you can convert them
to numbers in a variety of ways:

=VALUE(LEFT( ... ))
=LEFT( ... )*1
=LEFT( ... )+0
=--LEFT( ... )

so that you can then do further arithmetic on them. (Assumes that the
text is made up only of digits).

Hope this helps.

Pete

On Jul 31, 5:07*pm, DoubleZ wrote:
In Excel 2007, I have entries of the form: 500A+12. *I Would like to get rid
of the A and sum the numbers (i.e. return 512). *I can return 500 and 12
using the left, right, and len functions, but I'm assuming they are being
returned as text because when I sum them I get zero. *Is there another way to
do this? *If there is a formula, please also tell me what it would look like
if there were spaces involved (e.g. 500A + 12). *Thanks

Converting text to numbers with a twist
 

Thanks, Pete. That's even simpler than I thought.

"Pete_UK" wrote:

LEFT and RIGHT do indeed return text values, but you can convert them
to numbers in a variety of ways:

=VALUE(LEFT( ... ))
=LEFT( ... )*1
=LEFT( ... )+0
=--LEFT( ... )

so that you can then do further arithmetic on them. (Assumes that the
text is made up only of digits).

Hope this helps.

Pete

On Jul 31, 5:07 pm, DoubleZ wrote:
In Excel 2007, I have entries of the form: 500A+12. I Would like to get rid
of the A and sum the numbers (i.e. return 512). I can return 500 and 12
using the left, right, and len functions, but I'm assuming they are being
returned as text because when I sum them I get zero. Is there another way to
do this? If there is a formula, please also tell me what it would look like
if there were spaces involved (e.g. 500A + 12). Thanks

Converting text to numbers with a twist
 

The fastest way to do this if it is always an A followed by a plus sign,
assume you have column A with these, then make sure column B is empty, if
not select column B and do insertcolumn, that will create a new column B.
Then select column A, do datatext to columns, select delimited and click
next, select other and put A in the other box and click finished.

Now the 2 numbers are split in 2 column, select C1 and press Alt and =
and press enter

Select C1 again and move the cursor to the lower right corner of C1 and when
it changes from a thick cross to a thin double click. that will copy down
the formula.

Takes less than 30 seconds
--


Regards,


Peo Sjoblom

"DoubleZ" wrote in message
...
In Excel 2007, I have entries of the form: 500A+12. I Would like to get
rid
of the A and sum the numbers (i.e. return 512). I can return 500 and 12
using the left, right, and len functions, but I'm assuming they are being
returned as text because when I sum them I get zero. Is there another way
to
do this? If there is a formula, please also tell me what it would look
like
if there were spaces involved (e.g. 500A + 12). Thanks

Converting text to numbers with a twist
 

You're welcome - thanks for feeding back.

Pete

On Jul 31, 6:04*pm, DoubleZ wrote:
Thanks, Pete. *That's even simpler than I thought.