Why use a leading AND() in a formula?

[Russ1]

I was looking at this old post where a guy said to use a formula that started with AND() and only had 1 operator. From my testing, AND() with only operator doesn't seem to do anything other than give the same output of whatever boolean logic is inside it. (Put TRUE in it, returns TRUE. Put FALSE in it, returns FALSE.)

Retricting Special Characters using Data Validation

Hi, I have a requirement to restrict all the following special characters from being entered into a cell: \/:%'*?|". Have tried a few formulae but none worked in restricting all of them

www.mrexcel.com

I'm just wondering what the point of doing this is? I would have asked the guy but he apparently stopped posting in 2022 so he must be gone.

 

[Russ1]

There doesn't seem to be an "edit" button here so I will just say, maybe it has to do with just compressing the output of the formula to be only one value instead of many values in an array.

 

[FormR]

Hi, welcome to the forum! In your modern version of Excel you'll see that if you put the formula into a blank cell without the AND() function, that it returns an array of TRUE/FALSE values for each character in the cell its referencing. The AND() function test all the values in that array and only returns TRUE if they are all TRUE.

There's probably better ways to do it now with the newer functions.

Book1
	A	B	C
1	ab/d\fs	TRUE	FALSE
2		TRUE
3		FALSE
4		TRUE
5		FALSE
6		TRUE
7		TRUE
Sheet1

Cell Formulas
Range	Formula
B1:B7	B1	=ISERROR(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"\/:%'*?<>|""."))
C1	C1	=AND(B1#)
Dynamic array formulas.

 

[Russ1]

Hi, welcome to the forum! In your modern version of Excel you'll see that if you put the formula into a blank cell without the AND() function, that it returns an array of TRUE/FALSE values for each character in the cell its referencing. The AND() function test all the values in that array and only returns TRUE if they are all TRUE.

There's probably better ways to do it now with the newer functions.

Book1
	A	B	C
1	ab/d\fs	TRUE	FALSE
2		TRUE
3		FALSE
4		TRUE
5		FALSE
6		TRUE
7		TRUE
Sheet1

Cell Formulas
Range	Formula
B1:B7	B1	=ISERROR(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"\/:%'*?<>|""."))
C1	C1	=AND(B1#)
Dynamic array formulas.

Is the formula I referenced basically a For loop that starts at i=1 and goes to i=whatever length of the thing? That is clever, I hadn't thought to do that.

 

[FormR]

a For loop that starts at i=1 and goes to i=whatever length of the thing?

Hi, I'm not sure I'd use that exact analogy, but I guess it's pretty close

This part ROW(INDIRECT("1:"&LEN(A1))),1) returns a array of numbers starting at 1 and incrementing to the length of whatever is in the cell the formula is referencing. This is fed into the start_num parameter of the MID() function.