Link to home
Start Free TrialLog in
Avatar of Blowfelt82
Blowfelt82

asked on

Validate version numbers in Excel.

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.
Avatar of rspahitz
rspahitz
Flag of United States of America image

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

Avatar of redmondb
Hi, Blowfelt82.

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

Open in new window

Regards,
Brian.
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
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.
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. :)
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.
>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
Still OK with "8-8"?!
ASKER CERTIFIED SOLUTION
Avatar of rspahitz
rspahitz
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Nice, Rob. That gives Blowfelt82 maximum flexibility.
Avatar of Blowfelt82
Blowfelt82

ASKER

Thanks for the help.