CODEWITHSUNDEEP
excelexcel-formulavba
Slee
Slee

Reputation: 28248

Trim leading whitespace from Excel cells

How do I get rid of leading whitespaces in ?

I have quite a few rows with this problem.

Upvotes: 1

Views: 19450

Answers (3)

brettdj
brettdj

Reputation: 55672

On your whitespace removal request pls note that:

  • TRIM only removes character 32, ie a standard space.
  • CLEAN will remove non printing whitespace such as carriage returns (character 13) and linefeeds (character 10)
  • CLEAN doesn't deal with non-breaking spaces (character 160), a common issue with dealing with data from the web, for this you need a SUBSTITUTE function

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

  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

Upvotes: 10

Doc Brown
Doc Brown

Reputation: 20044

Lets suppose you want to get rid of whitespaces in column 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.

Upvotes: 5

Cᴏʀʏ
Cᴏʀʏ

Reputation: 107508

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

=TRIM(A1)

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

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.

Upvotes: 2

Related Questions