0

How can I force the format for this example in an Excel cell?

3.3H73 (number, period, number, letter, number number)

  • 1
    Use a custom formula in Data Validation. – Scott Craner Jul 25 '17 at 14:33
0

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 =ISERROR(...)):

enter custom data validation formula here

=IF(AND(ISNUMBER(VALUE(LEFT(A1,1))),MID(A1,2,1)=".",ISNUMBER(VALUE(MID(A1,3,1))),ISTEXT(MID(A1,4,1)),ISNUMBER(VALUE(RIGHT(A1,2))),LEN(A1)=6),1,0)

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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.