fasadreach.blogg.se

• #Microsoft word spaces between words too big code#

If You are familiar with collections, i once wrote a quick code that process the whole sheet even if it is huge and remove all double spaces, lead and trail spaces and invisible characters from all cells.

' Whitespace and all spaces between words are removed.ĭebug.Print "Character Count: " & Len(CleanUltra(myVar, True)) ' Optional parameter to remove spaces between words is set to TRUE. ' Whitespace is removed but spaces between words are preserved.ĭebug.Print "Character Count: " & Len(CleanUltra(myVar)) ' remove spaces between words is left blank it defaults to FALSE. ' Examples of various types of whitespaceĭebug.Print "Character Count: " & Len(myVar)

I had to set the function to remove it first, before the CLEAN and TRIM functions were used to stop them from removing all characters after the vbNullChar. Here's a test I ran to verify that the function actually removed all whitespace. Here's an example of it's usage: Sub Example() StringToClean = Replace(stringToClean, " ", vbNullString) If removeSpacesBetweenWords = True Then _ StringToClean = Application.Trim(stringToClean) ' Remove all spaces except single spaces between words StringToClean = Application.Clean(stringToClean) StringToClean = Replace(stringToClean, vbNullChar, vbNullString) ' Removes non-printable characters and whitespace from a string Optional ByVal removeSpacesBetweenWords As Boolean = False) _ Any improvements are welcome! Function CleanUltra( _ Are all your other functions leaving whitespace behind?ĬleanUltra removes all whitespace and non-printable characters including whitespace left behind by other functions!