Excel Macro remove space in prefix and suffix

This Excel tutorial explains how to use Excel Macro to remove space in prefix and suffix in all worksheets.

Excel Macro remove space in prefix and suffix

Most recently I performed data validation on Excel spreadsheet in which the data contained a lot of spaces in prefix and suffix. I guess this happened because they delimited the data string using space. For example, seperating first name from full name.

Suffix with space is not possible to identify by eye, but I had to remove all spaces in order for system upload. I could not use trim Function on every column because there were more than 200 columns with multiple worksheets, therefore I decided to create a Macro to identify all Cells with space in prefix and suffix, and then correct them automatically.

VBA Code – remove space in prefix and suffix

Public Sub checkSpace()
    If wsExists("contain space") Then
        Application.DisplayAlerts = False
        Sheets("contain space").Delete
        Application.DisplayAlerts = True
    End If
        Set newws = ActiveWorkbook.Worksheets.Add(before:=Worksheets(1))
        newws.Name = "contain space"
        newws.Range("A1").Value = "Worksheet"
        newws.Range("B1").Value = "Cell"

    For Each ws In ActiveWorkbook.Worksheets
        For Each Rng In ws.UsedRange
            If Not IsEmpty(Rng) And Len(Rng.Value) <> Len(Trim(Rng.Value)) Then
                nextrow = newws.Range("A" & Rows.Count).End(xlUp).Row + 1
                newws.Range("A" & nextrow).Value = ws.Name
                newws.Range("B" & nextrow).Value = Replace(Rng.Address, "$", "")
                newws.Hyperlinks.Add Anchor:=newws.Range("B" & nextrow), Address:="", SubAddress:="'" & ws.Name & "'!" & Rng.Address
                counter = counter + 1
            End If
    If IsEmpty(newws.Range("A2")) Then
        dummy = MsgBox("No space found", vbInformation)
        Application.DisplayAlerts = False
        Sheets("contain space").Delete
        Application.DisplayAlerts = True
        sinput = MsgBox(counter & " Cell(s) contain blank" & Chr(10) & "Do you want to automatically correct them now?" & Chr(10) & txt, vbOKCancel + vbExclamation)
        If sinput = 1 Then
            For r = 2 To newws.Range("A" & Rows.Count).End(xlUp).Row
                Sheets(newws.Range("A" & r).Value).Range(newws.Range("B" & r).Value).Value = Trim(Sheets(newws.Range("A" & r).Value).Range(newws.Range("B" & r).Value).Value)
            Next r
            dummy = MsgBox(counter & " Spaces removed", vbInformation)
        End If
    End If
End Sub

Explanation of Macro – remove space in prefix and suffix

This Macro loops through all worksheets. For each worksheet, it loops through the UsedRange

If a Range value is different from trimmed value, the Cell address and worksheet names are identified in a new worksheet called “contain space”.

contain space 11

After checking all UsedRange, a message box pops up asking if you want to correct all Cells in worksheet “contain space”. Click Yes to confirm.





Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *