Converting text to numbers with a twist

[DoubleZ]

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

 

[Pete_UK]

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

 

[DoubleZ]

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

 

[Peo Sjoblom]

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 insert>column, that will create a new column B.
Then select column A, do data>text 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

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete