Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Renfrewshire, Scotland
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #2
    New Lounger
    Join Date
    Dec 2009
    Location
    Santa Monica, CA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Renfrewshire, Scotland
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You are a star!!
    Thanks!

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    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,
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    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. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    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
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    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

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Zeddy,

    Ah! the Grasshopper must bow to the Master.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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