If format Question

[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

 

[John Wilson]

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

 

[ant]

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

.

 

[Harlan Grove]

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

...

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.

 

[John Wilson]

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