How can I force the format for this example in an Excel cell?
3.3H73 (number, period, number, letter, number number)
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.Sign up to join this community
You can do this using a slightly long custom data validation formula. On the ribbon, select DATA then Data Validation.
If the cell you want to validate is A1 then copy and paste this formula in here (in place of
Breaking this down, we're simply checking that every character in turn is either a number, text, or the
. character by position, and that the overall length of the string entered is correct.
=IF( AND( --- ALL conditions below must be TRUE ISNUMBER(VALUE(LEFT(A1,1))) --- char #1 must be a number ,MID(A1,2,1)="." --- char #2 must be . ,ISNUMBER(VALUE(MID(A1,3,1))) --- char #3 must be a number ,ISTEXT(MID(A1,4,1)) --- char #4 must be text ,ISNUMBER(VALUE(RIGHT(A1,2))) --- char #5&6 must be numbers ,LEN(A1)=6 --- the string must be 6 characters long ) ,1,0) --- if true output 1, else 0