The TRIM Function in Excel and VBA

Excel’s TRIM Function is a great way to remove extra spaces before, after, and within text in a spreadsheet. However, if you think the identically named VBA function does the same thing you’d be sadly mistaken, as I was once upon a time.

Here’s a normal sentence.

VBA TRIM Normal Sentence

Here’s a sentence with extra spaces – two before, one after, and several within the sentence.

TRIM Function Test Sentence 1

Here’s a comparison of the two TRIM Functions.

TRIM Function Comparison 2

The Excel TRIM function reemoves all spaces from text except for single spaces between words.

The VBA TRIM Function only removes leading and trailing spaces.

The VBA TRIM Function Warning

If you need to remove spaces within text and are using VBA Trim function, it’s best to use Application.WorksheetFunction.Trim(your text here) to get the job done.

Note: This is a public service message. 🙂