The TRIM Function in Excel and VBA

by Gregory on December 29, 2010

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.

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 TRIM Function Warning

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

Note: This is a public service message. 🙂

Related Posts Plugin for WordPress, Blogger...

Comments on this entry are closed.

Previous post:

Next post: