Removing extra spaces in a string

[John]

What's a quick way to remove multiple spaces between words in a string?

I tried ProjectName = Replace(ProjectName, " ", " "), but this sometimes
leaves multiple spaces between words in ProjectName.

I appreciate your help, -John

 

[Gord Dibben]

Application.Trim(string) to remove extra spaces


Gord Dibben MS Excel MVP

 

[Joel]

if you have 3 spaces your code will only remove one of the spaces still
leaving two try this

'Test for 2 blanks in a row
Do while Instr(ProjectName, " ") > 0 then
ProjectName = Replace(ProjectName, " ", " ")
Loop

 

[Rick Rothstein]

No "Then" keyword required in a Do statement. Also, while some may not
consider it as clear, you don't have to test the InStr function's return
value for being greater than 0... VB will consider any non-zero value for a
logical expression as True.

Do While InStr(ProjectName, " ")
ProjectName = Replace(ProjectName, " ", " ")
Loop