☰

删除小数点前的 0，除非其前面有一个非零数字

tag-icon • tag-icon microsoft-excel vba notepad++
删除小数点前的 0，除非其前面有一个非零数字

我有一列，每个单元格内都有逗号分隔的值，如下所示

0.1, 0.2,0.3, 0.4,0.5, 0.8,1.0
1.5, 1.6,2.0, 10.6,10.9, 15.2,30.75
20, 0.25,280.2, 0.29,300.2, 423,530.76

就像文本字符串。

目标是消除前导零在小数点前面，但仅当它前面没有其他数字（包括另一个 0）时我才使用搜索替换函数 vba： 

    选项明确
    Public Sub Replace0dot（可选 byDummy 作为字节）
        列(“A”).替换什么：“0。”，_
                            替换：=“.”，_
                            LookAt:=xlPart，_
                            搜索顺序：=xlByRows，_
                            匹配大小写:=False，_
                            搜索格式：=False，_
                            ReplaceFormat:=False
    应用程序.ScreenUpdating = True
    子目录结束

最后我得到了这个：

.1, .2,.3, .4,.5, .8,1
1.5, 1.6,2, 1.6,1.9, 15.2,3.75
2, .25,28.2, .29,30.2, 423,53.76

0.它删除了所有以开头的实例.，因此您会看到10.6变成了1.6。但它应该保留10.6 我如何获得搜索替换等效项，以便给我：

.1, .2,.3, .4,.5, .8,1
1.5, 1.6,2, 10.6,10.9, 15.2,30.75
20, .25,280.2, .29,300.2, 423,530.76

??? 似乎必须要取消连接并重新连接才能实现目标。

答案1

这是一个非常简单的方法：

  • 如果字符串开始和0。然后删除零
  • 如果字符串包含三胞胎喜欢{空格}0。然后去掉那个零
  • 如果字符串包含三胞胎喜欢,0.然后去掉那个零

选择单元格并运行此代码：

Sub fixdata()
    Dim r As Range, t As String

    For Each r In Selection
        t = r.Text
        If Left(t, 2) = "0." Then t = Mid(t, 2)
        t = Replace(t, " 0.", " .")
        t = Replace(t, ",0.", ",.")
        r.Value = t
    Next r
End Sub

前：

在此处输入图片描述

之后：

在此处输入图片描述

如果还有其他三元组需要更改，只需添加另一个Replace()

编辑＃1：

为了避免手动选择单元格，我们可以使用宏来执行此操作.........下面是列的示例A：

Sub fixdata2()
    Dim r As Range, t As String

    For Each r In Intersect(Range("A:A"), ActiveSheet.UsedRange)
        t = r.Text
        If Left(t, 2) = "0." Then t = Mid(t, 2)
        t = Replace(t, " 0.", " .")
        t = Replace(t, ",0.", ",.")
        r.Value = t
    Next r
End Sub

编辑＃2

在此版本中，我们附加了；在每个单元格末尾输入文本之前：

Sub fixdata3()
    Dim r As Range, t As String, Suffix As String
    Suffix = ";"

    For Each r In Intersect(Range("A:A"), ActiveSheet.UsedRange)
        t = r.Text
        If Left(t, 2) = "0." Then t = Mid(t, 2)
        t = Replace(t, " 0.", " .")
        t = Replace(t, ",0.", ",.")
        r.Value = t & Suffix
    Next r
End Sub

编辑3＃：

在此版本中；仅当单元格中尚不存在时才会附加：

Sub fixdata4()
    Dim r As Range, t As String, Suffix As String
    Suffix = ";"

    For Each r In Intersect(Range("A:A"), ActiveSheet.UsedRange)
        t = r.Text
        If Left(t, 2) = "0." Then t = Mid(t, 2)
        t = Replace(t, " 0.", " .")
        t = Replace(t, ",0.", ",.")
        If Right(t, 1) <> Suffix Then
            r.Value = t & Suffix
        End If
    Next r
End Sub

编辑＃4：

此版本不会影响空单元格： 

Sub fixdata5()
    Dim r As Range, t As String, Suffix As String
    Suffix = ";"

    For Each r In Intersect(Range("A:A"), ActiveSheet.UsedRange)
        t = r.Text
        If t <> "" Then
            If Left(t, 2) = "0." Then t = Mid(t, 2)
            t = Replace(t, " 0.", " .")
            t = Replace(t, ",0.", ",.")
            If Right(t, 1) <> Suffix Then
                r.Value = t & Suffix
            End If
        End If
    Next r
End Sub

编辑＃5：

这修复了上一个版本中的错误：

Sub fixdata6()
    Dim r As Range, t As String, Suffix As String
    Suffix = ";"

    For Each r In Intersect(Range("A:A"), ActiveSheet.UsedRange)
        t = r.Text
        If t <> "" Then
            If Left(t, 2) = "0." Then t = Mid(t, 2)
            t = Replace(t, " 0.", " .")
            t = Replace(t, ",0.", ",.")
            If Right(t, 1) <> Suffix Then
                t = t & Suffix
            End If
            r.Value = t
        End If
    Next r
End Sub

答案2

使用此 VBA 代码它将测试每个字符串的前导零

Sub Replace0dot()

   Dim str As String    
   Dim ln As Long   
   Dim i As Long    

   ln = Range("A1").End(xlDown).Row   
   For i = 1 To ln
   str = Cells(i, 1).Value
   If Left(str, 1) = "0" Then
   Cells(i, 1) = Mid(str, 2)
   End If
   Next i

End Sub

答案3

假设你仍然使用 notepad++ 中的行您可以使用数组代替文本到列

Sub notepadthingrevisit()
    Dim workingRange As Range
    Set workingRange = Range("A1:A3")
    Dim i As Long
    Dim j As Long
    Dim result As String

    Dim myStrings() As String
    For i = 1 To workingRange.Rows.Count
        myStrings = Split(Cells(i, 1), ",")
        'Adjust this for accounting for the first value and remember to trim the " " at the end
        For j = 0 To UBound(myStrings)
            If Left(Trim(myStrings(j)), 1) = 0 Then
                myStrings(j) = Right(Trim(myStrings(j)), (Len(Trim(myStrings(j))) - 1))
            End If
            If myStrings(j) = Int(myStrings(j)) Then myStrings(j) = Int(myStrings(j))
            'Check here for j mod x and insert " "
            result = result & myStrings(j) & ","
        Next
        Cells(i, 5) = result
    Next

End Sub

只需使用前一个线程中的信息来插入空格即可。

相关内容