IsNumber & Mid function

I have a cell (B2) that contains "RT3ERIDU"

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.

"El Bee" wrote:

> I have a cell (B2) that contains "RT3ERIDU"
>
> 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?

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.
"bpeltzer" <bpeltzer.deletethissuffix@cox.net> wrote in message
news:847C00B8-2905-4E3A-9834-0556BAD8A041@microsoft.com...
> 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.
>
> "El Bee" wrote:
>
>> I have a cell (B2) that contains "RT3ERIDU"
>>
>> 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?

Alan, Thanks!

This is a much easier and maintainable formula.

"Alan" wrote:

> 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.
> "bpeltzer" <bpeltzer.deletethissuffix@cox.net> wrote in message
> news:847C00B8-2905-4E3A-9834-0556BAD8A041@microsoft.com...
> > 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.
> >
> > "El Bee" wrote:
> >
> >> I have a cell (B2) that contains "RT3ERIDU"
> >>
> >> 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?
>
>
>