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.

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

"(E-Mail Removed)" wrote:

> 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.
>

Give this formula a try...

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

Rick


<(E-Mail Removed)> wrote in message
news:2feee408-ecd5-4135-8420-(E-Mail Removed)...
>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.

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


"Reg" <(E-Mail Removed)> wrote in message
news:AA4DBDE0-D1DD-466B-8160-(E-Mail Removed)...
> 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
>
> "(E-Mail Removed)" wrote:
>
>> 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.
>>

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!!