Why doesn't this work? - Replace function

Recently I've been trying to remove all instances of double spaces from a string. i.e. if I have a string

Please Login or Register  to view this content.

, I want to return "Test Me"


I tried using the Replace function but I was surprised by the result. (Code provided at bottom of post to demonstrate)


QUESTIONS:

1. Why doesn't Replace remove all double spaces?
2. Since Replace doesn't do what I want it to, what can I use instead?

DEMONSTRATION OF REPLACE NOT REMOVING ALL DOUBLE SPACES

Please Login or Register  to view this content.

Originally Posted by AB33

Please Login or Register  to view this content.

Am I doing something wrong? When I use that code I get:

Please Login or Register  to view this content.

I am looking for:

Please Login or Register  to view this content.

Why not use Application.Trim?

Please Login or Register  to view this content.

Brilliant Norie! +1


Strangely enough, Trim was one of the first things I tried and it didn't work. Is there an application version of Trim that is different to Trim?

nvm. Realised that Application.Trim = WorksheetFunction.Trim. For those like me who didn't know, read Help pages for Trim and WorksheetFunction.Trim. (One removes trailing & leading spaces, the other removes all spaces from text except for single spaces between words).

The worksheet function Trim will remove remove all spaces between words apart from single spaces.

Application.Trim is shorthand for Application.WorksheetFunction.Trim.

In answer to the why doesn't replace work.

It because it replaces first occurance of double space and then moves forward through the text. It does not start again from the begining of the text.
The issue can more easily be seen by using a character rather than space.

Please Login or Register  to view this content.

So you would need to loop whilst checking for existence of double spaces.

Please Login or Register  to view this content.

@ Andy Pope: That explains it. Thank you +1