Answers for "excel vba remove spaces from string"

excel vba clean extra spaces from an entire range

'VBA function to clean data from range and return an array. This
'function does NOT use loops:

Function CleanMAX(r As Range)
    CleanMAX = Replace("trim(clean(substitute(|,char(160),"" "")))", "|", r.Address)
    If r.Cells.Count > 1 Then CleanMAX = "index(" & CleanMAX & ",)"
    CleanMAX = Evaluate(CleanMAX)
End Function

'--------------------------------------------------------------------

'Clean the value and returns a SCALAR, not an array:
v = CleanMAX([a1])

'Clean every value and create a 2D array with 10 rows and 26 columns:
v = CleanMAX([a1:z10])

'--------------------------------------------------------------------

'Note:  this function also removes non-breaking space (ASCII 160).

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

vba remove spaces

Dim s As String
s = "  abc  "
Debug.Print "|" & LTrim(s) & "|"         ' |abc  |
Debug.Print "|" & RTrim(s) & "|"         ' |  abc|
Debug.Print "|" & Trim(s) & "|"          ' |abc|