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

Application.Trim(string) to remove extra spaces


Gord Dibben MS Excel MVP

On Wed, 4 Feb 2009 11:41:59 -0800, John
wrote:

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

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


"John" wrote:

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

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

--
Rick (MVP - Excel)


"Joel" wrote in message
...
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


"John" wrote:

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