Excel Formula To Check If All Characters Are Text Non-Numeric

[Rick Rothstein]

@rick

Did you consider using offset ?

=MAX(ABS(CODE(MID(UPPER(A1),ROW(OFFSET(A1,,,LEN(A1))),1))-77.5))<13 

I do not see what the benefit of using OFFSET has over using INDIRECT... your formula, like mine, still needs to be array-entered and it is still Volatile. Although I find the construction awkward, the following modification to my formula has the benefit of being non-Volatile (still needs to be array-entered** though)...

=MAX(ABS(CODE(MID(UPPER(A1),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1))-77.5))<13

**Commit this formula using CTRL+SHIFT+ENTER, not just Enter by itself.

[NITIN SHETTY]

I do not see what the benefit of using OFFSET has over using INDIRECT... your formula, like mine, still needs to be array-entered and it is still Volatile. Although I find the construction awkward, the following modification to my formula has the benefit of being non-Volatile (still needs to be array-entered** though)...

=MAX(ABS(CODE(MID(UPPER(A1),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1))-77.5))<13

**Commit this formula using CTRL+SHIFT+ENTER, not just Enter by itself.

Hi Rick,

Thanks for helping but it is not working for special characters say like abc.. der

Thanks

Nitin

[Rick Rothstein]

Hi Rick,

Thanks for helping but it is not working for special characters say like abc.. der

That is probably because you did not follow my instructions about array-entering** the formula...

**Commit this formula using CTRL+SHIFT+ENTER, not just Enter by itself.