Excel上的文本和数字格式 [英] Text and Number Format on Excel

问题描述

我有问题，当我想比较a1 = 1.3 b1 = 3和a2 = 1.3 b2 = 2 on excel

i have question that when i want to compare between a1= 1.3 b1= 3 and a2= 1.3 b2= 2 on excel

例如= IF（a1> a2，是，IF（a1 = a2，IF（b1> b2，是，否），否））

for example =IF(a1>a2,"Yes",IF(a1=a2,IF(b1>b2,"Yes","No"),"no"))

第一次没关系，但是我改变了从b1 = 1的值改为否，然后我改变了回到b1 = 3它仍然是否

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

另一个关心的是如果我使用1.3.1比较1.3.1.2它没有任何问题

another concern is if i use like 1.3.1 compare to 1.3.1.2 it doesn't have any problem

但是当我只使用1.3（一个小数点）它总是有问题

but when i use only 1.3 (one decimal point) it alway have problem

很难解释你应该尝试比较xx和xxx

it's hard to explain you should try to compare between x.x and x.x.x

如果你有一个很好的另一个VBA代码来比较版本号

also if you have a good another VBA code to compare version number

谢谢你，请告诉我我想学习一下吧

thank you please tell me i want to study about it

1小数点excel看到数量但是很多分数al point excel see as text

it like 1 decimal point excel see as number but many decimal point excel see as text

我如何解决这个问题（我已经尝试改变文本格式，但是在您更改单元格中的值后，它会工作1次） 。）

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.)

由于我的英文很差。使它更清楚我如何使xx看作为文本，以便我可以与xxx比较:)

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 :) "

推荐答案

这是一篇你可能想查看的文章/文章。我对您的问题的第一印象是您可能正在尝试进行版本号比较。您的文本中的点分隔符的长度和数量可能非常重要。现在请检查一下，

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-最高价值

否则您可以尝试日志：

= A1 * 10 ^（4-INT（LOG（A1）））

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

或者在尾部。点，当然第二个文本变成小数：

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

例如1.3.4将为1.34和1.3.4.1.3将为1.3413

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

1.2.5.6将为125.6和1.2.4.6.1将为124.61

1.2.5.6 will be 125.6 and 1.2.4.6.1 will be 124.61

PS：不在机器前面。将为您提供另外一个基于分割点分隔符和代码的代码。

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

使用函数进行编辑：这将比较两个版本号与任意数量的点数，将其视为字符串/文本。但是在1.3.1和1.21.1的情况下，这是最高的1.21.1。

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

输出：

这篇关于Excel上的文本和数字格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！