vba code to remove leading and trailing spaces

excel vba remove leading or trailing spaces in an entire column of data

'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).

Posted by: Guest on March-30-2020

Source

Option Explicit Function trimWhiteSpace(s As String) As String Dim RE As Object: Set RE = CreateObject("vbscript.regexp") With RE .Global = True .MultiLine = True .Pattern = "^s*(S.*S)s*" trimWhiteSpace = .Replace(s, "$1") End With End Function

Code answers related to "VBA"

Browse Popular Code Answers by Language

Answers for "vba code to remove leading and trailing spaces"

Code answers related to "vba code to remove leading and trailing spaces"

Code answers related to "VBA"

Browse Popular Code Answers by Language

Popular Programming Languages

Advertisements

Company

Compilers

Help

Connect with us