Alpha numeric excel data validation

taliapaw

New Member
Joined
Sep 14, 2016
Messages
16
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

Well-known Member
Joined
Oct 24, 2012
Messages
3,392
try
=AND(ISNUMBER(LEFT(A1,4)*1),ISTEXT(MID(A1,5,2)),ISNUMBER(MID(A1,8,5)*1),LEN(A1)=11)
 
Last edited:

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,392
correction
=AND(ISNUMBER(LEFT(A1,4)*1),ISTEXT(MID(A1,5,2)),ISNUMBER(MID(A1,7,5)*1),LEN(A1)=11)
 

taliapaw

New Member
Joined
Sep 14, 2016
Messages
16
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!
 

taliapaw

New Member
Joined
Sep 14, 2016
Messages
16
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)
 

Forum statistics

Threads
1,082,569
Messages
5,366,360
Members
400,886
Latest member
Fchel

Some videos you may like

This Week's Hot Topics

Top