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.