ID Validation using macros in excel

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...

Design a formula in XL that evaluates to TRUE/FALSE for
correct/incorrect ID's

Use Data->validation and use option Formula. and use the created
formula to evaluate ID's on cell data entry.

Dm Unseen

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...
>
>

Hi, Sridhar,

I couldn't get DaveO's solution to work, so i came up with this:

Sub ValidateString()

'Characters 1-5 are non-numeric
'Character 5 = "P"
'Characters 6-9 are numbers
'Last Character is non-numeric

Dim StrCellValue As String
Dim TestCell As Range

For Each TestCell In Selection

StrCellValue = TestCell.Text

If Not (IsNumeric(Left(StrCellValue, 5))) _
And Mid(StrCellValue, 4, 1) = "P" _
And IsNumeric(Mid(StrCellValue, 6, 4)) _
And Not (IsNumeric(Right(StrCellValue, 1))) Then
MsgBox ("Value in row " & TestCell.Row & " - Format
OK")
Else
MsgBox ("Value in row " & TestCell.Row & " - Format Incorrect")
End If
Next

End Sub

The macro works for a range of cells, as against just one - highlight all
the cells you want to test before you run this macro - it should be OK

Regards

Pete



"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...
>
>

Thank you, peter, its working for me.

Thanks again