Cropping text from a number

  • Thread starter Thread starter Matt
  • Start date Start date
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
 
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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Matt,

Just another alternative that doesn't require helper
columns:

A1 = *82
B1 = 85

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

Biff
 
Back
Top