Text and Number Format on Excel

Question

I have question that when I want to compare between a1= 1.3 b1= 3 and a2= 1.3 b2= 2 on excel for example =IF(a1>a2,"Yes",IF(a1=a2,IF(b1>b2,"Yes","No"),"no"))

First time it's okay, but then I changed value from b1 =1 it changed to "no" then I changed it back to b1= 3 it's still "no"

I think it concern on format in the cell.

Another concern is if I use like 1.3.1 compare to 1.3.1.2 it doesn't have any problem, but when I use only 1.3 (one decimal point) it always have problem

It's hard to explain you should try to compare between x.x and x.x.x also if you have a good another VBA code to compare version number

It like 1 decimal point excel see as number, but many decimal point excel see as text

How do I fix this (I have try change to text format but it work 1 time after you change value in cell it's go back to error.)

Due to my English is poor. to make it clearer "How do i make x.x see as text so that i can compare with x.x.x :) "

Answer 1

Here is an article/ post you may want to check. The first impression I had on your question is that you may be trying to do a version number comparison. Length and number of dot delimiters in your text could matter greatly.for now please check this,

http://www.dbforums.com/microsoft-excel/1670840-compare-version-numbers-return-highest-value.html

Or else you may try the log as well:

=A1*10^(4-INT(LOG(A1)))

Or do a replace on trailing . dots and surely the second text becomes a decimal:

E.g. 1.3.4 will be 1.34 and 1.3.4.1.3 will be 1.3413

1.2.5.6 will be 125.6 and 1.2.4.6.1 will be 124.61

PS: not front of a machine. Will provide you with another code I have based on split by dot delimiter and compare.

Edit with a function: this will compare two version numbers with any number of dot points, treating it as a string/text. However in the case of 1.3.1 and 1.21.1 this takes 1.21.1 as the highest number.

Option Explicit

Function versionNumberComparison(ByRef rng1 As Range, ByRef rng2 As Range) As String
    Dim i As Integer
    Dim arrVersion1 As Variant, arrVersion2 As Variant
    Dim strVer1 As String, strVer2 As String
    Dim bool2 As Boolean, bool1 As Boolean
    Dim x As Long, y As Long

    Application.EnableEvents = False
    If Not IsEmpty(rng1.Value) Then
        strVer1 = rng1.Value
        arrVersion1 = Split(rng1.Value, ".")
    Else
        versionNumberComparison = "Version number empty"
        GoTo Zoo
    End If
    If Not IsEmpty(rng2.Value) Then
        strVer2 = rng2.Value
        arrVersion2 = Split(rng2.Value, ".")
    Else
        versionNumberComparison = "Version number empty"
        GoTo Zoo
    End If

    If UBound(arrVersion1) > UBound(arrVersion2) Then
        x = UBound(arrVersion1)
        y = UBound(arrVersion2)
    ElseIf UBound(arrVersion1) < UBound(arrVersion2) Then
        x = UBound(arrVersion2)
        y = UBound(arrVersion1)
    Else
        x = UBound(arrVersion1)
        y = x
    End If

    i = 0
        While i <= y
            If IsNumeric(arrVersion1(i)) And IsNumeric(arrVersion2(i)) Then
                    If CInt(Trim(arrVersion1(i))) = CInt(Trim(arrVersion2(i))) Then
                        If i = y Then
                            If x <> y Then
                                If Len(strVer1) > Len(strVer2) Then
                                    bool1 = True
                                    bool2 = False
                                    GoTo PrintOut
                                Else
                                    bool2 = True
                                    bool1 = False
                                    GoTo PrintOut
                                End If
                            End If
                        End If
                            bool1 = False
                            bool2 = False
                    ElseIf CInt(Trim(arrVersion1(i))) > CInt(Trim(arrVersion2(i))) Then
                        bool1 = True
                        bool2 = False
                        GoTo PrintOut
                    Else
                        bool2 = True
                        bool1 = False
                        GoTo PrintOut
                    End If
            Else
                versionNumberComparison = "Enter Valid version numbers"
                GoTo Zoo
            End If
            i = i + 1
        Wend

PrintOut:

    If bool1 Then
        versionNumberComparison = strVer1
    ElseIf bool2 Then
        versionNumberComparison = strVer2
    Else
        versionNumberComparison = "Both the same"
    End If

Zoo:
Application.EnableEvents = True
End Function

Output: