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
Next
Next
If IsEmpty(newws.Range("A2")) Then
dummy = MsgBox("No space found", vbInformation)
Application.DisplayAlerts = False
Sheets("contain space").Delete
Application.DisplayAlerts = True
Else
newws.Columns("A:B").EntireColumn.AutoFit
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”.
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.
