excel add trailing spaces to text
=LEFT(A1 & REPT(" ",10),10)excel add trailing spaces to text
=LEFT(A1 & REPT(" ",10),10)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).Copyright © 2021 Codeinu
Forgot your account's password or having trouble logging into your Account? Don't worry, we'll help you to get back your account. Enter your email address and we'll send you a recovery link to reset your password. If you are experiencing problems resetting your password contact us
