How to use vba to remove extra spaces between words

Hi all,

I am trying to remove extra spaces between words that are being inputed by a userform. I have tried uisng "= Trim(TextBox1.Text)" which works nicely for leading spaces and end spaces but is not effective for extra spaces between words. Anybody have an idea of what vba code is needed to remove the extra spaces between words??

Thanks!

possibly not the most efficient but one method:

Please Login or Register  to view this content.

Is there any way to apply this code to an entire section on a worksheet??

Please Login or Register  to view this content.

This will remove upto 4 extra spaces in Column A:

Please Login or Register  to view this content.

Note: the worksheet function version of trim does remove spaces between words, so you could also do something like:

Please Login or Register  to view this content.

which would probably run faster for big ranges.

as an option

Please Login or Register  to view this content.

Thanks for all the Help!

@abousetta, I think in VBA it only removes leading and following spaces...

I think to utilise the worksheet function you would need to do something like in post 6?

@ yudlugar

abousetta is correct.

VBa Trim() only removes leading and trailing spaces
The Excel function TRIM() removes all extra spaces

In VBa call the Excel function like so

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Thanks Marcol, I assumed the worksheetfunction wouldn't be available as there was a vba version.

I have a macro with this in it as i get a lot of exported data with eccess spaces.
basically it will turn all double spaces into single spaces and loop untill no double spaces remain

Please Login or Register  to view this content.

Originally Posted by Marcol

@ Leon V (AW

Why?
When Worksheetfunction.Trim() does the job without looping, see post #9

Your code will not work properly if there are multiple leading or trailing spaces

I use vba trim for leading and trailing. Thought Excels trim doesn't work on some Unix spaces like ASCII 160