Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Validate version numbers in Excel.

Posted on 2013-05-22
11
Medium Priority
?
319 Views
Last Modified: 2013-07-04
I have a column in Excel that contains version numbers in the format 'n.n' where there can technically be any number of 'n' numbers e.g. 'n.n' is valid, 'n.n.n' is valid and so on. Using Excel data validation how can I create a custom validation rule to check for values that do not comply to this.
0
Comment
Question by:Blowfelt82
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
11 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 39188267
I don't think that data validation can easily handle this, but here's a macro you can run on the current cell (which could be extended to apply to the whole sheet or a predetermined range):

Sub ValidateCurrentCell()
    Dim strVersions() As String
    Dim bValidVersion As Boolean
    Dim iVersionCntr As Integer
    
    strVersions = Split(ActiveCell.Value, ".")
    bValidVersion = True
    For iVersionCntr = 0 To UBound(strVersions)
        If Val(strVersions(iVersionCntr)) = 0 And Not strVersions(iVersionCntr) Like String(Len(strVersions(iVersionCntr)), "0") Then
            bValidVersion = False
        End If
    Next
    
    If Not bValidVersion Then
        MsgBox "The current cell does not contain a valid version in the format of ##[.##[.##]...]"
    Else
        MsgBox "Version is valid"
    End If
End Sub

Open in new window

0
 
LVL 26

Expert Comment

by:redmondb
ID: 39188363
Hi, Blowfelt82.

Please try the following Data Validation formula...
=ISNUMBER(1*SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ","a"),"..","a"),".",""))

Open in new window

Regards,
Brian.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 39188392
Great formula Brian.  I guess you should also substitute out the "-", like -11.-11

For mine, to handle this, on line 9 change
...= 0

to ...<= 0
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Expert Comment

by:redmondb
ID: 39188840
Thanks, rspahitz. Frankly, I didn't consider that negative numbers were valid.

It's not enough to just substitute "-", otherwise "8-8" would be valid, so...
=ISNUMBER(1*SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,".-","")," ","a"),"..","a"),".",""))

Open in new window

...an initial "-" being OK.

Regards,
Brian.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 39188874
Good point about 8-8 (so ".-" is good), but also the initial "-" if it's there.  I guess that comes down to the source of all of this potentially bad data so maybe all the extra effort is not needed.

Also, I tried "tricking" it with other cases and couldn't find any, so that should wrap it up, but I still stand by my initial comment "I don't think that data validation can easily handle this" but I love your idea of thinking out of the box to handle it. :)
0
 
LVL 26

Expert Comment

by:redmondb
ID: 39188946
rspahitz,

I guess our boxes are in different places - I thought of the formula immediately, but a macro like yours would have taken me time.

BTW, "other cases" - try "1..2".    :)

Regards,
Brian.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 39189028
>try "1..2".

I saw that case and decided that "nothing" (the value between two dots) was essentially zero and therefore valid...but as I think further, it should probably be excluded by replacing the IF with the following:

        If strVersions(iVersionCntr) = "" _
        Or Val(strVersions(iVersionCntr)) <= 0 _
        And Not strVersions(iVersionCntr) Like String(Len(strVersions(iVersionCntr)), "0") Then

Open in new window


--
Rob
0
 
LVL 26

Expert Comment

by:redmondb
ID: 39189108
Still OK with "8-8"?!
0
 
LVL 22

Accepted Solution

by:
rspahitz earned 2000 total points
ID: 39189333
Very well then...here's a cleaned up version, turned into a function, which, I suppose, you could use check for true/false like this: =ValidateCurrentCell(A1)

where A1 is the cell to check

Function ValidateCurrentCell(SomeCell As Range) As Boolean
    Dim strVersions() As String
    Dim bValidVersion As Boolean
    Dim iVersionCntr As Integer
    
    strVersions = Split(SomeCell.Value, ".")
    bValidVersion = True
    For iVersionCntr = 0 To UBound(strVersions)
        If strVersions(iVersionCntr) = "" _
        Or Not strVersions(iVersionCntr) Like String(Len(strVersions(iVersionCntr)), "#") Then
            bValidVersion = False
            Exit For
        End If
    Next
    
'    If Not bValidVersion Then
'        MsgBox "The current cell does not contain a valid version in the format of ##[.##[.##]...]"
'    Else
'        MsgBox "Version is valid"
'    End If
    ValidateCurrentCell = bValidVersion
End Function

Open in new window

0
 
LVL 26

Expert Comment

by:redmondb
ID: 39189373
Nice, Rob. That gives Blowfelt82 maximum flexibility.
0
 

Author Closing Comment

by:Blowfelt82
ID: 39299570
Thanks for the help.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question