excel vba remove all spaces from string code example

Example: visual basic excel remove spaces

' NOTE: this function is for Microsoft Excel only (language: VBA)

'===============================================================================
'>> RemoveSpaceCharsInSelectedCells()
'===============================================================================
' Removes the spaces in the selected cells
'===============================================================================
Sub RemoveSpaceCharsInSelectedCells()

    Dim sFunct As String: sFunct = "RemoveSpaceCharsInSelectedCells"

    Dim bDebugging As Boolean: bDebugging = False
    
    If (bDebugging = True) Then
        Debug.Print Format(DateTime.Now, "hh:mm:ss") & " INFO " & sFunct & "| " _
        & "Running.. [Cells:" & Selection.Address & "]"
    End If
        
    '*********************************
    ' VALIDATIONS and declarations
    '*********************************
    '(DECLARATIONS)
    Dim wbInit As Workbook: Set wbInit = ActiveWorkbook
    Dim wsInit As Worksheet: Set wsInit = ActiveSheet
    Dim s_rInit As String: s_rInit = Selection.Address
    
    Dim sErrMsg As String
    
    Dim sReplaceRng As String
    Dim sReplaceString As String
    Dim sNewString As String
    
    '(SETTINGS/SETUP)
    Application.ScreenUpdating = False

    sReplaceRng = Selection.Address
    ' sReplaceString = " "
    sNewString = ""
    
    '---------------------------------
    '               WORK
    '---------------------------------
    '1) Replace the relevant cells containing the sReplaceString
    '   a) Remove the normal space value (ascii: 32)
    '   b) Remove the html space value (ascii: 160)
    '   c) Remove the html space value (ascii: nbsp)
    'Z) Reactivate the initial workbook/worksheet
        
    '--(1.a)
    sReplaceString = Chr(32) ' normal space (ascii: 32)
    Selection.Replace What:=sReplaceString, Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
        
    '--(1.b)
    sReplaceString = Chr(160) ' html number's space (ascii: 160)
    Selection.Replace What:=sReplaceString, Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
        
    '--(1.c)
    sReplaceString = Chr(nbsp) ' html name's space (ascii: 160)
    Selection.Replace What:=sReplaceString, Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    
    '--(Z)
    wbInit.Activate
    wsInit.Activate
    Range(s_rInit).Select
    
    '-----------v-----------DEBUG INFO-----------v-----------
    
    If (bDebugging = True) Then
        Debug.Print Format(DateTime.Now, "hh:mm:ss") & " INFO " & sFunct & "| " _
        & "Complete for cells [" & Selection.Address & "]"
    End If
    
    Application.ScreenUpdating = True
        
End Sub