Trim all cells values in entire worksheet using VBA

The TRIM function was designed to trim the 7-bit ASCII space character (value 32) from text. In the Unicode character set, there is an additional space character called the nonbreaking space character that has a decimal value of 160.

This character is commonly used in Web pages as the HTML entity,  . By itself, the TRIM function does not remove this nonbreaking space character.

We get data from many sources like web pages, databases, software outputs and many more. Copying a webpage data to excel, will have non breakable space characters, linefeed characters and carriage return characters in any portion of a sentence. 

Using Trim function alone we can not remove that characters. It only removes leading and trailing spaces from the text.

Here is a solution to remove that unwanted characters. If data has all the non breakable space characters, linefeed characters and carriage return characters, this subroutine will converts all the above said characters to normal space character. After converting to normal space character it will execute Trim function.

Trim - Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing.

Syntax:

Function Trim(String)

The required string argument is any valid string expression. If string contains Null, Null is returned.

Sub TrimCellsEntireWorksheet() 'Assigning # of rows and columns to trim intTotalRows = Cells.SpecialCells(xlLastCell).Row intTotalCols = Cells.SpecialCells(xlLastCell).Column For rwIndex = 2 To intTotalRows 'First row is header, so here ignore the header row For colIndex = 1 To intTotalCols 'Replaces Non-Breakable Space Character (Char(160)) to Space Character(Char(32)) Do While InStr(Cells(rwIndex, colIndex).Value, Chr(160)) Cells(rwIndex, colIndex).Value = Replace(Cells(rwIndex, colIndex).Value, Chr(160), " ") Loop 'Replaces Linefeed Character (Chr(10)) to Space Character(Char(32)) Do While InStr(Cells(rwIndex, colIndex).Value, "" & Chr(10) & "") Cells(rwIndex, colIndex).Value = Replace(Cells(rwIndex, colIndex).Value, "" & Chr(10) & "", " ") Loop 'Replaces Carriage Return Character (Chr(13)) to Space Character(Char(32)) Do While InStr(Cells(rwIndex, colIndex).Value, "" & Chr(13) & "") Cells(rwIndex, colIndex).Value = Replace(Cells(rwIndex, colIndex).Value, "" & Chr(13) & "", " ") Loop 'Replaces Double Spaces " " to Single Space " " Do While InStr(Cells(rwIndex, colIndex).Value, " ") Cells(rwIndex, colIndex).Value = Replace(Cells(rwIndex, colIndex).Value, " ", " ") Loop 'Removes Leading and Trailing spaces from the text Cells(rwIndex, colIndex).Value = Trim(Cells(rwIndex, colIndex).Value) Next colIndex Next rwIndex MsgBox "Trimming of Cell values in entire worksheet is completed", , "Trim - Cell Values on entire worksheet" End Sub