To check whether a cell only contains a-z or 1-9 characters?

[x-amount]

Is there a formula to show (true or false) whether a cell contains any characters that aren't a letter or number without resorting to vba?

Cheers,

x

 

[ThomasB]

Hi

Try this

IF(AND(NOT(ISNUMBER(G21)),NOT(ISTEXT(G21))),"Not Letter or Number","Is letter or number")

Rgs

Thomas

 

[x-amount]

I think that will show if the whole cell is either a letter or number, I will have (allowed) alphanumeric combinations of the 2

 

[ThomasB]

Sorry got the wrong end of the stick

make a list of the letters,number in one column J in my example

a
b
c
etc
etc
1
2
3
etc

then use this formula

=IF(ISERROR(VLOOKUP($K$10,$J$10:$J$44,1,0)),"Not letter or Number","Is letter or number")

K10 is the cell to be checked

if you had less than 29 values could have used the CHOOSE function instead of the VLOOKUP then you would not require a lookup column


Rgs Thomas

 

[Jonmo1]

Try this

=(SUMPRODUCT(--(MOD(MATCH(CODE(MID(A$1,ROW(A1:INDEX(A:A,LEN(A1))),1)),{0,48,58,65,91,97,123}),2)=0)))=LEN(A1)

 

[x-amount]

Try this

=(SUMPRODUCT(--(MOD(MATCH(CODE(MID(A$1,ROW(A1:INDEX(A:A,LEN(A1))),1)),{0,48,58,65,91,97,123}),2)=0)))=LEN(A1)

This place needs a smilie to do the shocked face!

Astounding formula, seems to work too!

I don't suppose you could expand upon how it works?!

Thanks, ever so much, that is really different level stuff!

 

[Jonmo1]

I don't suppose you could expand upon how it works?!

I was a fraid you were going to ask that.....

This place needs a smilie to do the shocked face!

click the "Go Advanced" button next to "Submit Reply". You get alot more text editing options, including smileys.

OK..I'll break it down slowly 1 part at a time.

Given example text in A1

ABc123

=(SUMPRODUCT(--(MOD(MATCH(CODE(MID(A$1,ROW(A1:INDEX(A:A,LEN(A1))),1)),{0,48,58,65,91,97,123}),2)=0)))=LEN(A1)

Most people know what len does, returns lentght of string.
Our example string has 6 charachters, so replace each instance of len with 6

=(SUMPRODUCT(--(MOD(MATCH(CODE(MID(A$1,ROW(A1:INDEX(A:A,6)),1)),{0,48,58,65,91,97,123}),2)=0)))=6

Index returns a range object based on INDEX(Range,Row#,Col#)
Col# is optional
So Index(A:A,6) = A6

=(SUMPRODUCT(--(MOD(MATCH(CODE(MID(A$1,ROW(A1:A6),1)),{0,48,58,65,91,97,123}),2)=0)))=6

Now the sumproduct makes it an array formula that will do the same calculation X number of times based on how large the array range is. In this case, it's A1:A6, so it calculates 6 times (A1 then A2 then A3 etc..)

For now just imagine it's doing it on A1 First
ROW of coarse returns the row # of a reference.
So first it uses the row # of A1 - 1

=(SUMPRODUCT(--(MOD(MATCH(CODE(MID(A$1,1,1)),{0,48,58,65,91,97,123}),2)=0)))=6

Mid of coarse returns the specified character(s) in the string, in this case, the 1st character, 1 character long (A)

=(SUMPRODUCT(--(MOD(MATCH(CODE("A"),{0,48,58,65,91,97,123}),2)=0)))=6

Chode returns the ASCII Number of the specified character.
For A, that is 65

=(SUMPRODUCT(--(MOD(MATCH(65,{0,48,58,65,91,97,123}),2)=0)))=6

Match returns the position # the lookup value (65) is found in the array.
since the 3rd option in Match is omitted, it searches for the Largest value that is Less than or equal to the lookup value (65)

So in this case, the smallest value in the array that is Less than or equal to 65 is 48. 48 is in position # 2 of the array. So match returns 2

=(SUMPRODUCT(--(MOD(2,2)=0)))=6

Mod returns the REMAINDER after 1 value is devided by another.
Example, MOD(9,2)
9/2 = 4 Remainder 1
MOD(13,5)
13/5 = 2 Remainder 3
MOD(23,8)
23/8 = 2 Remainder 7

Mod returns the REMAINDER

So 2/2 = 0 Remainder = 0 so Mod returns 0

=(SUMPRODUCT(--(0=0)))=6

0=0 is an expression (Question) with a true false answer.
If 0 = 0 it returns true, if 0 < > 0 it returns false
=(SUMPRODUCT(--(TRUE)))=6

The -- converts TRUE to 1 False to 0

=(SUMPRODUCT(1))=6

Now, don't do the = 6 yet, because it's outside the last parenthesis, the sumproduct must finish calculating first.
So now it goes back to the ROW part of the calculation. It repeats the same calculation again, but incriments to A2 Then to A3 Then to A4 etc..
Summing the result of each.
And if the result = 6 then the (Sumproduct(6)=6) will return TRUE.

The key part to understand is this
MOD(MATCH(CODE(MID(A$1,1,1)),{0,48,58,65,91,97,123}),2)=0

And understanding that A1 will incriment all the way to A6 (or whatever length of the string is). If you have a string of 20 characters, it loops from A1 to A20. And using The ROW # of A1-A20 at each loop.

so that incriments the position number used in MID.

And the ASCII characters that are important are

0-9 = 48-57
A-Z = 65-90
a-z = 97-122

MOD(MATCH(CODE(MID(A$1,1,1)),{0,48,58,65,91,97,123}),2)=0

Notice the gaps between each group of characters.
So the MATCH will return an EVEN number (2,4 or 6) for allowed characters 0-9 A-Z and a-z
Match will return an ODD number (1 3 5 or 7) for ANYTHING ELSE.

That's where the MOD comes in. On EVEN Numbers, MOD will = 0, on Odd Numbers, MOD will = 1.

Then if any character in the string is NOT a number or letter, MOD will = 1. And if MOD = 1, then that is NOT counted in the sumproduct, because the sumproduct is counting MOD=0

So if the total number of EVEN # Matches = 6 (length of string) the the formula returns TRUE

Hope that helps...

 

[Jonmo1]

OOOPS, in my explaination, this line

So in this case, the smallest value in the array that is Less than or equal to 65 is 48. 48 is in position # 2 of the array. So match returns 2

should read

So in this case, the smallest value in the array that is Less than or equal to 65 is 65. 65 is in position # 4 of the array. So match returns 4

 

[x-amount]

:eeek


That is borderline genius!!!

Thanks once again for sharing the explanation with me, I understand it entirely and can only award you the biggest kudos for coming up with that.

Thanks!

 

[Jonmo1]

Glad to help.

There's one thing to be aware of though. The use of ROW in the formula makes it breakable. If you insert a row above A1, it will change the formula from

=(SUMPRODUCT(--(MOD(MATCH(CODE(MID(A$1,ROW(A1:INDEX(A:A,LEN(A1))),1)),{0,48,58,65,91,97,123}),2)=0)))=LEN(A1)

To

=(SUMPRODUCT(--(MOD(MATCH(CODE(MID(A$2,ROW(A2:INDEX(A:A,LEN(A2))),1)),{0,48,58,65,91,97,123}),2)=0)))=LEN(A2)

The bold red A2 is the one that will through the formula off. Because it will now start with the Row# of A2 (2) in the MID Function, therefore skipping the first character of the string.

That can be resolved like this

=(SUMPRODUCT(--(MOD(MATCH(CODE(MID(A$1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)),{0,48,58,65,91,97,123}),2)=0)))=LEN(A1)

That will make it ALWAYS start with the Row# of A1. However, that will significantly increase the formula's calculation time. And may bog down your sheet too much.

So if you're not planning on inserting rows, or don't mind having to fix the formula when you do, don't worry about it.