vba remove leading and trailing spaces code example

Example 1: 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).

Example 2: vba remove leading blank space in string

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