[已解决]如何删除excel数据后面的空格

lsl20081984

excel数据，很多相同的数据但是就是因为第二行数据后面多一个空格导致重复数据用宏删除不了，求助宏，或者公式

例如

达飞基德 CAM CGA JADE

达飞基德  CMA CGM JADE

但就是第二行中CMA CGM JADE后有一个空格

最佳答案

月排行榜 / 总排行榜

搁浅2008

2009-1-1 14:02

去除符号

Sub 去除特殊符号()
    Application.ScreenUpdating = False
    Dim x%, y%, i%, j%, n%, m%
    Dim arr1, arr2, arr3()
    x = Range("A65536").End(xlUp).Row
    arr1 = Range("A2:B" & x)
    ReDim arr2(1 To UBound(arr1, 1), 1 To 2)
    For i = 1 To UBound(arr1, 1)
        If Right(arr1(i, 1), 1) = Right(Cells(2, 1), 1) Then
            arr2(i, 1) = Left(arr1(i, 1), Len(arr1(i, 1)) - 1)
            If arr1(i, 2) <> "" Then
                arr2(i, 2) = Left(arr1(i, 2), Len(arr1(i, 2)) - 1)
            Else
                arr2(i, 2) = ""
            End If
        Else
            arr2(i, 1) = arr1(i, 1)
            arr2(i, 2) = arr1(i, 2)
        End If
    Next i
    Range("A2").Resize(UBound(arr2, 1), 2) = arr2
    Application.ScreenUpdating = True
End Sub

[此贴子已经被作者于2009-1-1 14:06:44编辑过]

跳转到最佳答案楼层

搁浅2008

otI57aPh.rar (210.81 KB, 下载次数: 5)

2009-1-1 13:59 上传

点击文件名下载附件
如何删除excel数据后面的空格
Sub 删除重复()
    Application.ScreenUpdating = False
    Dim x%, y%, i%, j%, n%, m%
    Dim arr1, arr2, arr3()
    Range("D2:F65536").ClearContents
    x = Range("A65536").End(xlUp).Row
    arr1 = Range("A2:B" & x)
    ReDim arr2(1 To UBound(arr1, 1), 1 To 2)
    For i = 1 To UBound(arr1, 1)
        If Right(arr1(i, 1), 1) = Right(Cells(2, 1), 1) Then
            arr2(i, 1) = Left(arr1(i, 1), Len(arr1(i, 1)) - 1)
            If arr1(i, 2) <> "" Then
                arr2(i, 2) = Left(arr1(i, 2), Len(arr1(i, 2)) - 1)
            Else
                arr2(i, 2) = ""
            End If
        Else
            arr2(i, 1) = arr1(i, 1)
            arr2(i, 2) = arr1(i, 2)
        End If
    Next i
    '*********************去除完特殊符号***********************
    n = 1
    For j = 1 To UBound(arr2, 1)
        m = 0
        ReDim Preserve arr3(1 To 2, 1 To n)
        For x = 1 To UBound(arr3, 2)
            If arr2(j, 1) = arr3(1, x) Then
                m = m + 1
                Exit For
            End If
        Next x
        If m = 0 Then
            arr3(1, n) = arr2(j, 1)
            arr3(2, n) = arr2(j, 2)
            n = n + 1
        End If
    Next j
    Range("D2").Resize(n - 1, 2) = Application.Transpose(arr3)
    Application.ScreenUpdating = True
End Sub
你这个不是空格 我也不知道是什么

If Right(arr1(i, 1), 1) = Right(Cells(2, 1), 1) Then
我直接这样判断的

搁浅2008

去除符号

Sub 去除特殊符号()
    Application.ScreenUpdating = False
    Dim x%, y%, i%, j%, n%, m%
    Dim arr1, arr2, arr3()
    x = Range("A65536").End(xlUp).Row
    arr1 = Range("A2:B" & x)
    ReDim arr2(1 To UBound(arr1, 1), 1 To 2)
    For i = 1 To UBound(arr1, 1)
        If Right(arr1(i, 1), 1) = Right(Cells(2, 1), 1) Then
            arr2(i, 1) = Left(arr1(i, 1), Len(arr1(i, 1)) - 1)
            If arr1(i, 2) <> "" Then
                arr2(i, 2) = Left(arr1(i, 2), Len(arr1(i, 2)) - 1)
            Else
                arr2(i, 2) = ""
            End If
        Else
            arr2(i, 1) = arr1(i, 1)
            arr2(i, 2) = arr1(i, 2)
        End If
    Next i
    Range("A2").Resize(UBound(arr2, 1), 2) = arr2
    Application.ScreenUpdating = True
End Sub

[此贴子已经被作者于2009-1-1 14:06:44编辑过]

领风飞翔

[em02][em02][em02]

我要学VB

lsl20081984

高手啊，呵呵，我这个不太懂，宏也是拷贝别人的，不过工作中很有用，你的那个宏挺有用但是还是删除不全空格，好像，呵呵，能加你MSN吗，我normanleee@hotmail.com

ymlhl

我连看都看不懂，这个论坛的高手太多了，而且都是高深莫测。

HSS

的确高手多，所以本论坛才可以真正学到东西。

向各位老师潜心学习！

yaoxuejian

[em02]我要学VB