software

I have a cell (B2) that contains quot;RT3ERIDUquot;

I wrote a simple formula =IF(ISNUMBER(MID(B2,3,1)),LEFT(B2,3),LEFT(B2,2))

If the third character is a number I want to extract the first 3 characters
otherwise extract the first 2.

This isn't working and I don't understand why.
If I setup another cell with just the MID function then the cell displays
the number 3.

What am I missing here?

MID returns a string, so the isnumber function returns false, even if it's a
text representation of a number. I'd use iserror(value()) instead:
=if(iserror(value(mid(b2,3,1))),left(b2,2),left(b2 ,3))
If mid doesn't return a digit, then the isserror(value()) will be true and
this only returns two digits. Otherwise iserror is false and you get three
digits.

quot;El Beequot; wrote:

gt; I have a cell (B2) that contains quot;RT3ERIDUquot;
gt;
gt; I wrote a simple formula =IF(ISNUMBER(MID(B2,3,1)),LEFT(B2,3),LEFT(B2,2))
gt;
gt; If the third character is a number I want to extract the first 3 characters
gt; otherwise extract the first 2.
gt;
gt; This isn't working and I don't understand why.
gt; If I setup another cell with just the MID function then the cell displays
gt; the number 3.
gt;
gt; What am I missing here?

Another way,
=IF(ISNUMBER(MID(B2,3,1)*1),LEFT(B2,3),LEFT(B2,2))
the *1 forces the text value into a numerical value,
Regards,
Alan.
quot;bpeltzerquot; gt; wrote in message
...
gt; MID returns a string, so the isnumber function returns false, even if it's
gt; a
gt; text representation of a number. I'd use iserror(value()) instead:
gt; =if(iserror(value(mid(b2,3,1))),left(b2,2),left(b2 ,3))
gt; If mid doesn't return a digit, then the isserror(value()) will be true and
gt; this only returns two digits. Otherwise iserror is false and you get
gt; three
gt; digits.
gt;
gt; quot;El Beequot; wrote:
gt;
gt;gt; I have a cell (B2) that contains quot;RT3ERIDUquot;
gt;gt;
gt;gt; I wrote a simple formula =IF(ISNUMBER(MID(B2,3,1)),LEFT(B2,3),LEFT(B2,2))
gt;gt;
gt;gt; If the third character is a number I want to extract the first 3
gt;gt; characters
gt;gt; otherwise extract the first 2.
gt;gt;
gt;gt; This isn't working and I don't understand why.
gt;gt; If I setup another cell with just the MID function then the cell displays
gt;gt; the number 3.
gt;gt;
gt;gt; What am I missing here?
Alan, Thanks!

This is a much easier and maintainable formula.

quot;Alanquot; wrote:

gt; Another way,
gt; =IF(ISNUMBER(MID(B2,3,1)*1),LEFT(B2,3),LEFT(B2,2))
gt; the *1 forces the text value into a numerical value,
gt; Regards,
gt; Alan.
gt; quot;bpeltzerquot; gt; wrote in message
gt; ...
gt; gt; MID returns a string, so the isnumber function returns false, even if it's
gt; gt; a
gt; gt; text representation of a number. I'd use iserror(value()) instead:
gt; gt; =if(iserror(value(mid(b2,3,1))),left(b2,2),left(b2 ,3))
gt; gt; If mid doesn't return a digit, then the isserror(value()) will be true and
gt; gt; this only returns two digits. Otherwise iserror is false and you get
gt; gt; three
gt; gt; digits.
gt; gt;
gt; gt; quot;El Beequot; wrote:
gt; gt;
gt; gt;gt; I have a cell (B2) that contains quot;RT3ERIDUquot;
gt; gt;gt;
gt; gt;gt; I wrote a simple formula =IF(ISNUMBER(MID(B2,3,1)),LEFT(B2,3),LEFT(B2,2))
gt; gt;gt;
gt; gt;gt; If the third character is a number I want to extract the first 3
gt; gt;gt; characters
gt; gt;gt; otherwise extract the first 2.
gt; gt;gt;
gt; gt;gt; This isn't working and I don't understand why.
gt; gt;gt; If I setup another cell with just the MID function then the cell displays
gt; gt;gt; the number 3.
gt; gt;gt;
gt; gt;gt; What am I missing here?
gt;
gt;
gt;