Alpha numeric excel data validation

[taliapaw]

Hi, I'm trying to restrict the entry of data into a cell so it follows this format 1234AA12345 however after trying many formulas I can't get it to work any help would be greatly appreciated.

 

[etaf]

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

 

[etaf]

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

 

[taliapaw]

Awesome thank you Wayne the second one works prefect! I was stuck on this for two days using formulas way more complex. Have a lovely day and thanks again!

 

[etaf]

you are welcome

 

[taliapaw]

Is there any way to modify the formula so that it forces uppercase for the text?
=AND(ISNUMBER(LEFT(A1,4)*1),ISTEXT(MID(A1,5,2)),ISNUMBER(MID(A1,7,5)*1),LEN(A1)=11)

I've tried something along the lines of this but it still allows for lowercase text
=AND(ISNUMBER(LEFT(A1,4)*1),UPPER(ISTEXT(MID(A1,5,2))),ISNUMBER(MID(A1,7,5)*1),LEN(A1)=11)