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