If format Question

  • Thread starter Thread starter Ant
  • Start date Start date
A

Ant

I want code to see if a cell is in this specific format

nnnnlll the N=a number and l=letter

so its 4 numbers and three letters

For example 0205cov

If its in this format =true

If not = false

Please Help
 
Ant,

Using range A1 on the Activesheet....

Sub TestMe()
If UCase(ActiveSheet.Range("A1")) Like "####[A-Z][A-Z][A-Z]" Then
MsgBox "True"
Else
MsgBox "False"
End If
End Sub

John
 
is there a way without using VB????

Thanks
-----Original Message-----
Ant,

Using range A1 on the Activesheet....

Sub TestMe()
If UCase(ActiveSheet.Range("A1")) Like "####[A-Z][A-Z][A- Z]" Then
MsgBox "True"
Else
MsgBox "False"
End If
End Sub

John
I want code to see if a cell is in this specific format

nnnnlll the N=a number and l=letter

so its 4 numbers and three letters

For example 0205cov

If its in this format =true

If not = false

Please Help
Click to expand...

.
Click to expand...
 
I want code to see if a cell is in this specific format

nnnnlll the N=a number and l=letter

so its 4 numbers and three letters

For example 0205cov
Click to expand...
...

I'll assume upper and lower case are equally acceptable. Try either

=AND(ISNUMBER(-MID(A3,{1,2,3,4},1)),
ABS(CODE(UPPER(MID(A3&" ",{5,6,7},1)))-77.5)<13)

or

=AND(ISNUMBER(FIND(MID(UPPER(A1),{1,2,3,4;5,6,7,8},1),
{"01234567890";"ABCDEFGHIJKLMNOPQRSTUVWXYZ"})))

The first is more robust. The second is more flexible - easier to change what
each character should match, but it breaks if there are 8 or more characters in
A1 and the 8th isn't also a letter.
 
Harlan,

Thanks for the assist.
Forgot what group I was in when I posted my response.
Neat formula(s) too...I'll have to save them in my archives.

John
 
Back
Top