The function you;re looking for is TypeName.

So something like this might just work.

-----------------------------------------------

Sub YourSubName()

Dim strCellValue As String

strCellValue = Range("A1").Text

If TypeName(Left(strCellValue, 5)) = "String" And TypeName(Mid(strCellValue,
6, 4)) = "Double" _
And TypeName(Right(strCellValue, 1)) = "String" And Mid(strCellValue, 4,
1) = "p" Then
Do Something
Else
MsgBox "Not correct format"
End If

End Sub

------------------------------------------------------------

Not sure if you want to loop through all of them in one hit or what you want
to do if it's wrong, but this should be a good starter for 10 I hope.

HTH.

"Sridhar Machina" wrote:

> Hi All,
>
> I need help in creating a macro which can validate IDs listed in a
> column in spread sheet.
>
> There is a fixed format for each ID:
>
> The first 5 places of the ID should be characters.
> The next 4 should be numericals
> Last 1 should be a character
> 4th character from beginning should be "P"
>
> Examples of some of the IDs:
>
> ACCPR4243A
> BDKPZ6145M
> LNHPC3807D
>
> thank you all in advance.
>
> Sri...
>
>