The dreaded "E"

[mwilliams4]

I have a formula that gets the right 5 characters of a string and I
want to visually know if it's a number or has text characters in the
string.

Everything works great, except when there is an "E".
Then it comes back as a number an E1234 number

My formula is as follows
=IF(ISNUMBER((RIGHT(B42,5))*1)=FALSE,"TEXT","")

Is there a way to ignore an "E" representing a number?

Any help would be greatly appreciated.

 

[Reg]

have a look at rick rothsteins code in 'extract numbers and digits thread
below' or do a search on

If Not Value Like "*[!0-9]*" Then
Reg

 

[Rick Rothstein \(MVP - VB\)]

Give this formula a try...

=IF(ISNUMBER((SUBSTITUTE(LOWER(RIGHT(A5,5)),"e","x"))*1),"","TEXT")

Rick

 

[Rick Rothstein \(MVP - VB\)]

Thanks for the plug, but I don't think that approach will help the OP. While
I recognize this group is named "programming", the OP's question shows
him/her attempting to use a worksheet formula to accomplish his/her goal; so
I'm thinking a worksheet formula is desired for the solution.

Rick

have a look at rick rothsteins code in 'extract numbers and digits thread
below' or do a search on

If Not Value Like "*[!0-9]*" Then
Reg

I have a formula that gets the right 5 characters of a string and I
want to visually know if it's a number or has text characters in the
string.

Everything works great, except when there is an "E".
Then it comes back as a number an E1234 number

My formula is as follows
=IF(ISNUMBER((RIGHT(B42,5))*1)=FALSE,"TEXT","")

Is there a way to ignore an "E" representing a number?

Any help would be greatly appreciated.

 

[mwilliams4]

That's great guys, I'm sure that formula will do it.
And yes, I was looking for a formula, not code.
I also just realized that I may have not posted to the right group,
but you came to the rescue.

THANKS!!