Formula to determine format?

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

You are a star!!
Thanks!

Reply | Quote

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,

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RetiredGeek

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

..but nothing is ever final:
How about:

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

Reply | Quote

Zeddy,

Ah! the Grasshopper must bow to the Master.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote