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?

IsNumber & Mid function
 

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?

IsNumber & Mid function
 

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" wrote in message
...
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?

IsNumber & Mid function
 

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" wrote in message
...
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?