Cropping text from a number

M

Matt

Hi
I am doing a staight comparisions of two numbers in two
different cells (a percentage increase)

However one of the numbers I am using has an asterisk on
the left hand end of the number.

Obviously excel cannot calculate the increase given it is
not redaing the cell as a number.

How can I right a formula so that excell does not read the
asterisk rather only the number


any help would be appreciated
 
N

Norman Harker

Hi Matt!

You could set up an extra column to the right of the offending numbers
and use:

Data > Text to Columns

Or you could set up and extra column and parse the offending numbers
with the formula:

=--RIGHT(A1,LEN(A1)-1)
Or:
=VALUE(RIGHT(A1,LEN(A1)-1))

The formulas could be used in a formula:
e.g.
=A2/--RIGHT(A1,LEN(A1)-1)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
B

Biff

Hi Matt,

Just another alternative that doesn't require helper
columns:

A1 = *82
B1 = 85

=--SUBSTITUTE(A1,"*","")/B1

Biff
 
Top