In Excel, the TRIM function can be used to delete spaces from a text in a cell. The Trim function also exists in VBA, but it works a little differently and gives different results in some situations.
As an example we take the text:
" John Peter "returns:
"John Peter"The result of the VBA function:
Trim(" John Peter ")however is:
"John Peter"The difference is that the Excel functions TRIM also removes the extra spaces between words, while the VBA function removes only the spaces at the beginning and the end. To get the same effect with VBA as Excel worksheetfunction TRIM, can be used:
Application.Trim(" Joh Peter ")If you are not working in an Excel environment, the following code can be used as an alternative:
Replace(Replace(Replace(Trim(" John Peter "), " ", " ~"), "~ ", ""), " ~", " ")In VBA, besides Trim, there are also the LTrim and RTrim functions, where only the spaces on the left or right side are removed.
Formulas for the VBA Trim functions
For the VBA functions Trim, LTrim and RTrim, there are no standard functions in Excel that give exactly the same result. The following formulas can be used for this, with the sentence with spaces in cell A1:
For LTrim:
=MID(A1,SEARCH(LEFT(TRIM(A1),1),A1),LEN(A1))For RTrim:
=LEFT(A1,MAX(INDEX((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)<>" ")*ROW(INDIRECT("1:"&LEN(A1))),)))And for the VBA Trim function, that is a combination of both the above formulas:
=MID(A1,SEARCH(LEFT(TRIM(A1),1),A1),MAX(INDEX((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)<>" ")*ROW(INDIRECT("1:"&LEN(A1))),))-SEARCH(LEFT(TRIM(A1),1),A1)+1)
So, with this last formula, only the leading and trailing spaces are removed and all spaces in between are retained.
Questions / suggestions
Hopefully this article has helped you removing spaces with formulas and with VBA. If you have any questions about this topic or suggestions for improvement, please post a comment below.
