Check if cell text has a specific format
I am looking for a way to determine if text entered into an Excel cell is following this format: "0000/00/00" (0s can be any one digit number {0,1,2,3,4,5,6,7,8,9}).
Is there a way to check if the cell has a formatting of "0000/00/00"?
Things I already know:
- That I can check it using something DATEVALUE() function in a data validation rule for the cell, the problem with this method is that it would not accept 1001/04/07 as a valid date.
- Trying to see if the text contains two "/" characters and that its length is equal to 10 characters. some data validation formula like: "=OR(AND(LEN(A16)=10,(LEN(A16)-LEN(SUBSTITUTE(A16,"/",""))=2),NOT(FALSE)))", the problem with this validation rule is that it would accept entries like "0550//0014" as valid.
P.S: I am working in an environment where using macros or extensions is not allowed!
43 Answers
The following user defined function will return TRUE if the argument has the proper format otherwise False:
Option Explicit
Public Function FormatCheck(r As Range) As Boolean Dim s As String, s2 As String, arr, i As Long FormatCheck = False s = r(1).Text s2 = Replace(s, "/", "") If Len(s2) <> 8 Then Exit Function arr = Split(s, "/") If UBound(arr) <> 2 Then Exit Function If Len(arr(0)) <> 4 Then Exit Function If Len(arr(1)) <> 2 Then Exit Function If Len(arr(2)) <> 2 Then Exit Function For i = 1 To 8 If Not Mid(s2, i, 1) Like "[0-9]" Then Exit Function Next i FormatCheck = True
End FunctionIt's not the pretty formula in the world, but this might work for you. =IFERROR(IF(CONCATENATE(MID(A1,1,4),MID(A1,6,2),MID(A1,9,2))*1>1,"TRUE"),"FALSE")
Hope this helps
1Considering that @AlexM has given the answer as a comment and that asked me to answer my question using his strategy, here is what did exactly what I needed.
I set the Data validation rule to:
=IF(AND(ISNUMBER(VALUE(MID(A1,1,4))),ISNUMBER(VALUE(MID(A1,6,2))),ISNUMBER(VALUE(MID(A1,9,2))),MID(A1,5,1)="/",MID(A1,8,1)="/",LEN(A1)=10),TRUE)And here is what I got:
