How do I ignore a character in a cell?
 

I'm comparing numbers between two cells and I used the conditional
format to do this. But now I need to remove a character from the first
cell in order to get the right results. The first
cell would have something like this Q000003 which would be the quantity
and the second cell would be 2. And my condition is if first cell is
greater than second cell than the first cell would change colors. But I
need to ignore the Q in order to get the right results. How do I fix
this?

How do I ignore a character in a cell?
 

Assuming Q00003 is in cell A1 then:
=VALUE(MID(A1,2,255))
will return the numerical number 3.

Hope this helps
Rowan

wrote:
I'm comparing numbers between two cells and I used the conditional
format to do this. But now I need to remove a character from the first
cell in order to get the right results. The first
cell would have something like this Q000003 which would be the quantity
and the second cell would be 2. And my condition is if first cell is
greater than second cell than the first cell would change colors. But I
need to ignore the Q in order to get the right results. How do I fix
this?

How do I ignore a character in a cell?
 

rowan explain more to etdmai about the use of 255 there, other wise he
might think of it as essential.

yours
sajay


Assuming Q00003 is in cell A1 then:
=VALUE(MID(A1,2,255))
will return the numerical number 3.


Hope this helps
Rowan

How do I ignore a character in a cell?
 

Sajay

1. I'm not sure I am that open to taking orders in this newsgroup.
2. The matter has been resolved (I think) in another thread:
http://tinyurl.com/86y6w
3. In the verison of excel that I am using (2002) the third argument in
the MID worksheet function is not optional and hence is essential. While
the OP could adjust the number of characters used here to suit the data
I cannot as I do not know exactly what format the data will take in
every case.

Rowan

sajay wrote:
rowan explain more to etdmai about the use of 255 there, other wise he
might think of it as essential.

yours
sajay


Assuming Q00003 is in cell A1 then:
=VALUE(MID(A1,2,255))
will return the numerical number 3.


Hope this helps
Rowan

How do I ignore a character in a cell?
 

but explain why 255 and not any other sum
even i have doubts