# Thread: Formula to determine format?

1. Hi all,

I am trying to see if there is a way to check the format of the values within a cell.

I am about to receive through a number of files that will all have a column containing a code to determine each case we are working on. All our codes are of the format xxxxxx.xxxxxx (eg CE0305.001000 - though a small amount of these are all numerical) and I want a simple way to mark out which of the entries follow this pattern and which fall outwith.

Something along the lines of: if (cell format is xxxxxx.xxxxxx, true, false)

Anyone have any ideas?

Thanks

Alba

2. So basically the length of the string must be 13 characters long and there has to be a period in position 7. You can do this, then:
If the cell whose format you are checking is in A1, put this in another cell:

=+IF(OR(LEN(A1)<>13,MID(A1,7,1)<>"."),"Error!","")

If the length of the string in A1 is not 13, or if the 7th character is not a period, then the string "Error!" is output to the cell. You can add other conditions to your OR function as your needs become more detailed.

You can also use the formula to conditionally highlight the cell. That is, if the cell doesn't contain the correct format, then make the cell red.

3. You are a star!!
Thanks!

4. Alba,

If you want to do more thorough pattern testing you can use a user defined function as follows:

Place this code in any standard Module in the VBA Editor - Alt+F11
Code:
```Option Explicit

Public Function bPatternVerify(zVal As String) As Boolean

'*** Calling Sequence =bPatternVerify(cell Reference)
'*** i.e. if the value to check in in A1: =bPatternVerify(A1)
'*** can be dragged down a column to copy.

Dim iCnt As Integer

zVal = UCase(zVal)  '*** Remove if You only allow Upper Case Letters

If Not IsNumeric(zVal) Then
If Len(zVal) = 13 And _
Left(zVal, 1) >= Chr(64) And _
Mid(zVal, 2, 1) <= Chr(99) And _
Mid(zVal, 7, 1) = "." Then

For iCnt = 3 To 13
If iCnt <> 7 Then
If Not IsNumeric(Mid(zVal, iCnt, 1)) Then
bPatternVerify = False
Exit Function
End If
End If
Next iCnt

bPatternVerify = True
Else
bPatternVerify = False
End If

End If

End Function    '*** bPatternVerify ***```
I've attached a picture of my test results.

I hope you find this useful...if not I still had fun building it,

5. Hi RetiredGeek

Have you checked out the VBA Like operator?
I think it is very powerful and underused.

Heres my version of your function:

Public Function checkFormat(zVal As String) As Boolean

zVal = UCase(zVal)

If Len(zVal) <> 13 Then
checkFormat = False
Exit Function
End If

If Mid(zVal, 7, 1) <> "." Then
checkFormat = False
Exit Function
End If

If Not Left(zVal, 6) Like "???###" Then '?=allow any char; #=must be digit
checkFormat = False
Exit Function
End If

If Not Right(zVal, 6) Like "######" Then 'allows digits only, no letters etc
checkFormat = False
Exit Function
End If

checkFormat = True

End Function

There's lots of scope with using Like in VBA
For example, you can specify a set of allowable characters (e.g. allowing A,B,C etc but NOT Q,Z or K)
Check it out and have fun

zeddy

6. Edit: My previous post got lost - bad internet connection.
Zeddy, thanks much...I was looking for just this but couldn't bring it to mind. You get a thumbs up from me.

A Final Refinement.

Code:
```Public Function bCheckPattern(zVal As String) As Boolean

zVal = UCase(zVal)

If zVal Like "[A-Z][A-Z]####[.]######" Then bCheckPattern = True

End Function```
Reference: VB & VBA in a Nutshell, Paul Lomax, O'Rielly ISBN:1-56592-358-8 pgs: 403-405

7. Hi RetiredGeek

I loved your 'Final Refinement'.
You get a thumbs up from me.

..but nothing is ever final:

Public Function bCheckPattern(zVal As String) As Boolean

zVal = UCase(zVal)

bCheckPattern = zVal Like "[A-Z][A-Z]####[.]######"

End Function

..no ifs or buts

zeddy

8. Zeddy,

Ah! the Grasshopper must bow to the Master.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•