vba 从 Excel 单元格中修剪前导空格

Trim leading whitespace from Excel cells

提问by Slee

How do I get rid of leading whitespaces in excel?

如何摆脱excel中的前导空格？

I have quite a few rows with this problem.

我有很多关于这个问题的行。

回答by brettdj

On your whitespace removal request pls note that:

在您的空白删除请求中，请注意：

• TRIMonly removes character 32, ie a standard space.
• CLEANwill remove non printing whitespace such as carriage returns (character 13) and linefeeds (character 10)
• CLEANdoesn't deal with non-breaking spaces (character 160), a common issue with dealing with data from the web, for this you need a SUBSTITUTEfunction

• TRIM仅删除字符 32，即标准空格。
• CLEAN将删除非打印空格，例如回车（字符 13）和换行符（字符 10）
• CLEAN不处理不间断空格（字符 160），这是处理来自网络的数据的常见问题，为此您需要一个SUBSTITUTE函数

You can do this either with formulae over the entire cell, or more painlessly (and targeting the leading whitespace) with vba

您可以使用整个单元格上的公式执行此操作，也可以使用vba更轻松地（并针对前导空格）执行此操作

1. With Formula you can use combine these three formulae to clean an entire cell like so =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))). See Ron de Bruins's writeup here

2. The VBA below will efficiently replace your data in-situ using arrays and regular expressions without the need for any working columns and copying and pasting. Instructions for use are included with the code below. This code works only on the leading portion of the string, unlike the formula options

       Sub KillLeadingSpaces()
   
        'Press Alt + F11 to open the Visual Basic Editor (VBE)
        'From the Menu, choose Insert-Module.
        'Paste the code into the right-hand code window.
        'Press Alt + F11 to close the VBE
        'In Xl2003 Goto Tools ....Macro .... Macros and double-click KillLeadingSpaces
        'In Xl2007/10 Goto Developer .. Macros and double-click KillLeadingSpaces
   
       Dim rng1 As Range
       Dim rngArea As Range
       Dim lngRow As Long
       Dim lngCol As Long
       Dim lngCalc As Long
       Dim objReg As Object
       Dim X()  
   
       On Error Resume Next
       Set rng1 = Application.InputBox("Select range for the replacement of leading zeros", "User select", Selection.Address, , , , , 8)
       If rng1 Is Nothing Then Exit Sub
       On Error GoTo 0
   
       'See Patrick Matthews excellent article on using Regular Expressions with VBA
       Set objReg = CreateObject("vbscript.regexp")
       objReg.Pattern = "^[\s|\xA0]+"
   
       'Speed up the code by turning off screenupdating and setting calculation to manual
       'Disable any code events that may occur when writing to cells
       With Application
           lngCalc = .Calculation
           .ScreenUpdating = False
           .Calculation = xlCalculationManual
           .EnableEvents = False
       End With
   
       'Test each area in the user selected range
   
       'Non contiguous range areas are common when using SpecialCells to define specific cell types to work on
       For Each rngArea In rng1.Areas
           'The most common outcome is used for the True outcome to optimise code speed
           If rngArea.Cells.Count > 1 Then
              'If there is more than once cell then set the variant array to the dimensions of the range area
              'Using Value2 provides a useful speed improvement over Value. On my testing it was 2% on blank cells, up to 10% on non-blanks
               X = rngArea.Value2
               For lngRow = 1 To rngArea.Rows.Count
                   For lngCol = 1 To rngArea.Columns.Count
                       'replace the leading zeroes
                       X(lngRow, lngCol) = objReg.Replace(X(lngRow, lngCol), vbNullString)
                   Next lngCol
               Next lngRow
               'Dump the updated array sans leading whitepace back over the initial range
               rngArea.Value2 = X
           Else
               'caters for a single cell range area. No variant array required
               rngArea.Value = objReg.Replace(rngArea.Value, vbNullString)
           End If
       Next rngArea
   
       'cleanup the Application settings
       With Application
           .ScreenUpdating = True
           .Calculation = lngCalc
           .EnableEvents = True
       End With
   
       Set objReg = Nothing
       End Sub
   

1. 使用公式，您可以使用组合这三个公式来像这样清理整个单元格 =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))。在此处查看 Ron de Bruins 的文章

2. 下面的 VBA 将使用数组和正则表达式有效地原位替换您的数据，而无需任何工作列以及复制和粘贴。使用说明包含在下面的代码中。与公式选项不同，此代码仅适用于字符串的前导部分

       Sub KillLeadingSpaces()
   
        'Press Alt + F11 to open the Visual Basic Editor (VBE)
        'From the Menu, choose Insert-Module.
        'Paste the code into the right-hand code window.
        'Press Alt + F11 to close the VBE
        'In Xl2003 Goto Tools ....Macro .... Macros and double-click KillLeadingSpaces
        'In Xl2007/10 Goto Developer .. Macros and double-click KillLeadingSpaces
   
       Dim rng1 As Range
       Dim rngArea As Range
       Dim lngRow As Long
       Dim lngCol As Long
       Dim lngCalc As Long
       Dim objReg As Object
       Dim X()  
   
       On Error Resume Next
       Set rng1 = Application.InputBox("Select range for the replacement of leading zeros", "User select", Selection.Address, , , , , 8)
       If rng1 Is Nothing Then Exit Sub
       On Error GoTo 0
   
       'See Patrick Matthews excellent article on using Regular Expressions with VBA
       Set objReg = CreateObject("vbscript.regexp")
       objReg.Pattern = "^[\s|\xA0]+"
   
       'Speed up the code by turning off screenupdating and setting calculation to manual
       'Disable any code events that may occur when writing to cells
       With Application
           lngCalc = .Calculation
           .ScreenUpdating = False
           .Calculation = xlCalculationManual
           .EnableEvents = False
       End With
   
       'Test each area in the user selected range
   
       'Non contiguous range areas are common when using SpecialCells to define specific cell types to work on
       For Each rngArea In rng1.Areas
           'The most common outcome is used for the True outcome to optimise code speed
           If rngArea.Cells.Count > 1 Then
              'If there is more than once cell then set the variant array to the dimensions of the range area
              'Using Value2 provides a useful speed improvement over Value. On my testing it was 2% on blank cells, up to 10% on non-blanks
               X = rngArea.Value2
               For lngRow = 1 To rngArea.Rows.Count
                   For lngCol = 1 To rngArea.Columns.Count
                       'replace the leading zeroes
                       X(lngRow, lngCol) = objReg.Replace(X(lngRow, lngCol), vbNullString)
                   Next lngCol
               Next lngRow
               'Dump the updated array sans leading whitepace back over the initial range
               rngArea.Value2 = X
           Else
               'caters for a single cell range area. No variant array required
               rngArea.Value = objReg.Replace(rngArea.Value, vbNullString)
           End If
       Next rngArea
   
       'cleanup the Application settings
       With Application
           .ScreenUpdating = True
           .Calculation = lngCalc
           .EnableEvents = True
       End With
   
       Set objReg = Nothing
       End Sub
   

回答by Doc Brown

Lets suppose you want to get rid of whitespaces in column A.

假设您想删除 A 列中的空格。

1. Goto to an empty column (lets say B)
2. Enter =TRIM(A1)into B1.
3. Fill this formula downward into all rows of B.
4. Then copy column B to the clipboard and use "paste contents" to copy the values (not the formula) of column B back to column A.

1. 转到一个空列（比方说 B）
2. 进入=TRIM(A1)B1。
3. 将此公式向下填充到 B 的所有行中。
4. 然后将 B 列复制到剪贴板并使用“粘贴内容”将 B 列的值（而不是公式）复制回 A 列。

回答by C???

Can you use the built-in TRIMfunction? It removes whitespace from the beginning and end of the text passed into it:

可以使用内置TRIM函数吗？它从传递给它的文本的开头和结尾删除空格：

=TRIM(A1)

This formula would give you the text from cell A1 without leading or trailing whitepace.

此公式将为您提供单元格 A1 中的文本，而无需前导或尾随空格。

To use the formula, you would insert a column next to your original column. Then enter the formula into the first (top) empty cell, substituting the cell coordinates of the first (top) cell of your original column for "A1", then press enter. Then you can grab the bottom-right of the cell (cursor will change to a "+" symbol), and drag the box down to the last cell in the new column to repeat the formula for all rows in the column.

要使用该公式，您需要在原始列旁边插入一列。然后将公式输入到第一个（顶部）空单元格中，将原始列的第一个（顶部）单元格的单元格坐标替换为“A1”，然后按 Enter。然后您可以抓住单元格的右下角（光标将变为“+”符号），并将该框向下拖动到新列中的最后一个单元格，以对该列中的所有行重复该公式。