vba 如何删除文本之间的空格？

How to remove spaces in between text?

提问by niko

Why trim is not working in VBA?

为什么修剪在 VBA 中不起作用？

for i = 3 to 2000
activesheet.cells(i,"C").value = trim(Activesheet.cells(i,"C").value)
next i

It is unable to remove the spaces in between the text.

无法删除文本之间的空格。

 hiii              how ' even after trying trim the o/p is still these
 hiii              how

I need to remove the extra spaces so I found Trimto do it but it is not working while ltrimand rtrimare.

我需要让我发现，除去多余的空格Trim做到这一点，但它不工作时ltrim和rtrim是。

回答by aevanko

The VBA Trimfunctionis different than Excel's. Use Excel's Application.WorksheetFunction.Trimfunctioninstead.

该VBATrim函数是比Excel的不同。改用Excel 的Application.WorksheetFunction.Trim函数。

Excel Trim will remove all spaces except a single space between words. VBA Trim will remove leading and trailing spaces.

Excel Trim 将删除除单词之间的单个空格之外的所有空格。VBA Trim 将删除前导和尾随空格。

Thank MS for using the same keyword for different functions.

感谢 MS 为不同的功能使用相同的关键字。

回答by Mitch Wheat

Trim removes extra spaces at start and end, not in the middle of a string.

Trim 删除开头和结尾的多余空格，而不是字符串中间的空格。

Function CleanSpace(ByVal strIn As String) As String
    strIn = Trim(strIn)

  ' // Replace all double space pairings with single spaces
    Do While InStr(strIn, "  ")
        strIn = Replace(strIn, "  ", " ")
    Loop

    CleanSpace = strIn
End Function

From here.

从这里开始。

PS. It's not the most efficient way to remove spaces. I wouldn't use on many, very long strings or in a tight loop. It might be suitable for your situation.

附注。这不是删除空格的最有效方法。我不会在很多很长的弦上或在一个紧密的循环中使用。它可能适合您的情况。

回答by osknows

I know this question is old but I just found it and thought I'd add what I use to remove multiple spaces in VBA....

我知道这个问题很老，但我刚刚找到它并认为我会添加我用来删除 VBA 中多个空格的内容....

cleanString = Replace(Replace(Replace(Trim(cleanString), _ 
 " ", " |"), "| ", ""), " |", " ") 'reduce multiple spaces chr(32) to one

回答by Excel Developers

When you call Trim() VBA is actually calling Strings.Trim(). This function will only remove leading and trailing spaces. To remove excessive spaces within a string, use

当您调用 Trim() 时，VBA 实际上是在调用 Strings.Trim()。此函数只会删除前导和尾随空格。要删除字符串中过多的空格，请使用

Application.Trim()

回答by davinceleecode

I know this question is old but I just want to share my solution on how to deal and fix with this issue.

我知道这个问题很老，但我只想分享我关于如何处理和解决这个问题的解决方案。

Maybe you might wondering why sometimes TRIM functionisn't working, remember that it will only remove spaces and spaces are equivalent to ASCII 32. So if these ASCII 13or ASCII 10exists in the Beginningor endof your string value then TRIM functionwill not work on it.

也许您可能想知道为什么有时TRIM function不起作用，请记住它只会删除空格并且空格等效于ASCII 32。因此，如果这些ASCII 13或ASCII 10存在于字符串值的开头或结尾，TRIM function则将无法使用。

Function checkASCIItoBeRemoved(myVal) As String

Dim temp As String
temp = Replace(Trim(myVal), Chr(10), Empty)
temp = Replace(temp, Chr(13), Empty)
checkASCIItoBeRemoved = temp

End Function

With this code it works for me, by the way if this might not work on your side then try to check the ASCIIof you string value because it might have another invisible special charthat might not covered on my code to replace on it, kindly add on it to work.
Please see reference for someinvisible special char.

使用此代码，它对我有用，顺便说一句，如果这可能对您不起作用，请尝试检查您的字符串值的ASCII，因为它可能有另一个不可见的特殊字符，我的代码中可能没有覆盖它以替换它，请添加它来工作。
请参阅参考资料以了解一些不可见的特殊字符。

回答by ChrisB

Are all your other functions leaving whitespace behind?

您的所有其他功能是否都留下了空白？

Get CleanUltra!

获取 CleanUltra！

CleanUltra removes all whitespace and non-printable characters including whitespace left behind by other functions!

CleanUltra 删除所有空格和不可打印字符，包括其他函数留下的空格！

I hope you find this useful. Any improvements are welcome!

希望这个对你有帮助。欢迎任何改进！

Function CleanUltra( _
       ByVal stringToClean As String, _
       Optional ByVal removeSpacesBetweenWords As Boolean = False) _
        As String
' Removes non-printable characters and whitespace from a string


' Remove the 1 character vbNullChar. This must be done first
'  if the string contains vbNullChar
    stringToClean = Replace(stringToClean, vbNullChar, vbNullString)

    ' Remove non-printable characters.
    stringToClean = Application.Clean(stringToClean)

    ' Remove all spaces except single spaces between words
    stringToClean = Application.Trim(stringToClean)

    If removeSpacesBetweenWords = True Then _
       stringToClean = Replace(stringToClean, " ", vbNullString)

    CleanUltra = stringToClean
End Function

Here's an example of it's usage:

这是它的用法示例：

Sub Example()
    Dim myVar As String
    myVar = " abc d e  "

    MsgBox CleanUltra(myVar)
End Sub

Here's a test I ran to verify that the function actually removed all whitespace. vbNullCharwas particularly devious. I had to set the function to remove it first, before the CLEANand TRIMfunctions were used to stop them from removing all characters after the vbNullChar.

这是我运行的一个测试，以验证该函数是否确实删除了所有空格。 vbNullChar特别狡猾。我必须先设置函数以将其删除，然后使用CLEAN和TRIM函数阻止它们删除vbNullChar.

Sub Example()
    Dim whitespaceSample As String
    Dim myVar As String

' Examples of various types of whitespace
'  (vbNullChar is particularly devious!)
    whitespaceSample = vbNewLine & _
                       vbCrLf & _
                       vbVerticalTab & _
                       vbFormFeed & _
                       vbCr & _
                       vbLf & _
                       vbNullChar

    myVar = "     1234" & _
            whitespaceSample & _
            "     56      " & _
            "789     "

    Debug.Print "ORIGINAL"
    Debug.Print myVar
    Debug.Print "Character Count: " & Len(myVar)


    Debug.Print
    Debug.Print "CLEANED, Option FALSE"


    Debug.Print CleanUltra(myVar)
    Debug.Print CleanUltra(myVar, False)
    '   Both of these perform the same action.  If the optional parameter to
    '   remove spaces between words is left blank it defaults to FALSE.
    '   Whitespace is removed but spaces between words are preserved.
    Debug.Print "Character Count: " & Len(CleanUltra(myVar))


    Debug.Print
    Debug.Print "CLEANED, Option TRUE"

    Debug.Print CleanUltra(myVar, True)
    '   Optional parameter to remove spaces between words is set to TRUE.
    '   Whitespace and all spaces between words are removed.
    Debug.Print "Character Count: " & Len(CleanUltra(myVar, True))

End Sub

回答by baltman

My related issue was that the last character was a chr(160) - a non-breaking space. So trim(replace(Str,chr(160),"")) was the solution.

我的相关问题是最后一个字符是 chr(160) - 一个不间断的空格。所以 trim(replace(Str,chr(160),"")) 是解决方案。

回答by RobDogNZ

I know this is quite old but thought I'd add in something else rather than all these replace options.

我知道这已经很老了，但我想我会添加其他东西而不是所有这些替换选项。

Using trim (or trim$) in VBA will remove the leading and trailing spaces, which as mentioned is different from =TRIM in Excel.

在 VBA 中使用修剪（或修剪 $）将删除前导和尾随空格，如前所述，这与 Excel 中的 =TRIM 不同。

If you need to remove spaces (as mentioned below not necessarily all whitespace) from inside a string simply use WorksheetFunction.Trim.

如果您需要从字符串内部删除空格（如下所述，不一定是所有空格），只需使用WorksheetFunction.Trim.

回答by Brian Stevenson

Sometimes what looks to be a space is not a space but a character that cannot be displayed. Use the ASC function to get the integer value of the character. Then use the following code:

有时看起来是空格的不是空格而是无法显示的字符。使用 ASC 函数获取字符的整数值。然后使用以下代码：

Function CleanSpace(ByVal StrIn As String) As String
    StrIn = Trim(StrIn)

  ' Searches string from end and trims off excess ascii characters

  Dim StrLength As Integer
  Dim SingleChar As Integer
  Dim StrPosition As Integer

  SingleChar = 1

  StrLength = Len(StrIn)
  StrPosition = StrLength - 1

    Do Until Asc(Mid(StrIn, StrPosition, SingleChar)) <> 0
        StrPosition = StrPosition - 1
    Loop
  StrIn = Mid(StrIn, 1, StrPosition)

End Function

回答by user2971872

If You are familiar with collections, i once wrote a quick code that process the whole sheet even if it is huge and remove all double spaces, lead and trail spaces and invisible characters from all cells. Just take care it will remove the format of your text, i also did not do much testing and it's exhaustive but it worked for my short task and worked fast.

如果您熟悉集合，我曾经编写了一个快速代码来处理整个工作表，即使它很大，并从所有单元格中删除所有双空格、前导空格和尾随空格以及不可见字符。请注意它会删除您的文本格式，我也没有做太多测试，而且它很详尽，但它适用于我的简短任务并且工作得很快。

This is an Auxiliary function that loads the sheet into a collection

这是一个将工作表加载到集合中的辅助函数

Function LoadInCol() As Collection

Dim currColl As Collection
Dim currColl2 As Collection

Set currColl = New Collection
Set currColl2 = New Collection

With ActiveSheet.UsedRange
    LastCol = .Columns(.Columns.Count).Column
    lastrow = .Rows(.Rows.Count).Row
End With

For i = 1 To lastrow

    For j = 1 To LastCol
        currColl.Add Cells(i, j).Value
    Next

    currColl2.Add currColl
    Set currColl = New Collection

Next

    Set LoadInCol = currColl2

End Function

And this is the main Sub that removes the spaces

这是删除空格的主要 Sub

Sub RemoveDSpaces()

'Removes double spaces from the whole sheet

Dim Col1 As Collection
Dim Col2 As Collection
Dim Col3 As Collection

Dim StrIn As String

Dim Count As Long

Set Col1 = New Collection
Set Col2 = New Collection
Set Col3 = New Collection


Set Col1 = LoadInCol()

Count = Col1.Count

i = 0

For Each Item In Col1

i = i + 1
If i >= Count + 1 Then Exit For

    Set Col2 = Item

    For Each Item2 In Col2

        StrIn = WorksheetFunction.Clean(Trim(Item2))

        Do Until InStr(1, StrIn, "  ", vbBinaryCompare) = 0
            StrIn = Replace(StrIn, "  ", Chr(32))
        Loop

        Col3.Add StrIn

    Next

Col1.Remove (1)
Col1.Add Col3
Set Col3 = New Collection

Next

'Store Results

Cells.ClearContents

    Z = 1
    m = 1
    Set Col3 = New Collection

    For Each Item In Col1

        Set Col3 = Item

            For Each Item2 In Col3
                Cells(Z, m) = Item2
                m = m + 1
            Next

            m = 1
            Z = Z + 1

    Next

End Sub