the use of UPPER

[taliapaw]

Hi I've modified my formula or at least tried to because I want only upper case letters to be inputted for the data. My data format is as follows 1234AB12345 where AB must be capital. I've tried using the upper function in my formula but it still allows for lower case letters. I've been stuck on fixing this issue for two days now any help or suggestions would be great appreciated.

=AND(ISNUMBER(LEFT(A1,4)*1),UPPER(ISTEXT(MID(A1,5,2))),ISNUMBER(MID(A1,7,5)*1),LEN(A1)=11)

I'm using this formula in MS excel in data validation.

 

[peterhinton]

very basic, but just try =UPPER(A1)

edit: my bad didn't see the data validation part

 

[Jonmo1]

Try

=AND(ISNUMBER(LEFT(A1,4)*1),EXACT(UPPER(MID(A1,5,2)),MID(A1,5,2)),ISNUMBER(MID(A1,7,5)*1),LEN(A1)=11)

 

[wideboydixon]

Another possibility:

Book1
	A	B
1	1234ab12345	FALSE
2	1234AB12345	TRUE
3	12345A12345	FALSE
Sheet1

Cell Formulas
Range	Formula
B1	=AND(ISNUMBER(LEFT($A1,4)*1),(SUMPRODUCT(--(MATCH(CODE(MID($A1,{5,6},1)),{0,65,91,255})=2)))=2,ISNUMBER(MID($A1,7,5)*1),LEN($A1)=11)

WBD

 

[taliapaw]

Jonmo1 his works great, so basically the problem was I never separated the middle text. Thank you.


wideboydixon I now see an error in my original code however your formula isn't allowed to be used in data validation because of the use of reference operators (such as unions, intersections, and ranges) or array constants.

 

[Jonmo1]

You're welcome.