Edit Replace command

[Guest]

I have someone who's Replace command replaces everything in one pass. With
mine however I have to repeat the process. This is Excel 2003

An example is lets say you want to replace all occurrences of two spaces
with one. The first time the Replace command is run it only makes one pass
removing only the first found two spaces in each cell. I have to run the
command several times to replace all occurrences. But the other person only
has to run it once.

Is there a setting somewhere? How does yours work?

Thanks

 

[Rick Rothstein \(MVP - VB\)]

I have someone who's Replace command replaces everything in one pass. With

mine however I have to repeat the process. This is Excel 2003

An example is lets say you want to replace all occurrences of two spaces
with one. The first time the Replace command is run it only makes one pass
removing only the first found two spaces in each cell. I have to run the
command several times to replace all occurrences. But the other person
only has to run it once.

What is the other person replacing and what is he/she replacing it with? If
you wanted to replace every occurrence of a double space with something that
does not contain a space in it, then one pass is enough. The problem you
describes does not take into account an odd number of contiguous spaces. The
replacement of each pair leaves multiple spaces next to each other at the
end. Why? Because Replace is not reentrant (it doesn't look back to see what
it just created)... it simply looks for the double, space, replaces it, and
moves on to the next piece of text after the text it has just operated on.

Rick

 

[SteveT]

I don't think you read my post.

I want to know if anyone is finding Replace replaces all occurrences or does
just one pass and they have to run it again until all the occurences are
replaced.

It doesn't matter what the character is. To make this simple type the letter
a 26 times in every cell of one column (do it once and fill down). I did it
for 30,000 rows.

Choose Edit Replace
Find what: aa
Replace with: a
and click Replace All

On my system there will be more than one a left in each cell but on another
person's system they will only have one a left in each cell.
I believe in Excel 2000 you had to run it more than once.
I may have a system problem but I would like to know what other people are
finding, if anyone could do a test.

Thanks,
Steve

 

[Dave Peterson]

I used xl2003.

I put
aaaa
in a few cells
I did edit|replace and changed aa to a

The cells then contained
aa
(exactly two letters)

I think you're misremembering xl2k's behavior. As far as I can recall, this is
the way excel has always worked (xl95-xl2007).

I'd be disappointed if it did what you wrote.

Maybe that person had a macro that repeated the edit|replace or did something
else???????

 

[Roger Govier]

Hi Steve

You only need a few rows to demonstrate what happens.
With 6 "a"s in each cell, carrying out the Edit/Replace it finds 3 sets
of 2, does the replacement and you are left with 3 a's in each cell.
If you carry out the Edit/Replace again, this time it only finds one
occurrence of a double, which gets replaced with a single, and the
result is 2 a's in each cell.
If you did it a third time, then each pair would get replaced with a
single, and all cells would contain a single a.

The behaviour is the same in all versions of XL including XL2007.

 

[SteveT]

Thanks for both responses. I mentioned 30,000 rows because that is an actual
file we are using.

From what both of you wrote I understand that on your system it only makes
one pass meaning that out of 26 nets 13, out of 6 nets 3, 4 nets 2.

At least it's not my system.

I can't believe the other person's Replace command is running another macro.
Could there be a setting somewhere? Even a registry setting?

Thanks for your assistance,
Steve

 

[It's the Principle!]

I have someone who's Replace command replaces everything in one
pass. With mine however I have to repeat the process. This is
Excel 2003

An example is lets say you want to replace all occurrences of two
spaces with one. The first time the Replace command is run it only
makes one pass removing only the first found two spaces in each
cell. I have to run the command several times to replace all
occurrences. But the other person only has to run it once.

Is there a setting somewhere? How does yours work?

Thanks

You would have to run it again if in any particular place there were
three spaces. There is one find, one replace per instance. If you ran
a "find two, replace with one" it will do it and move on. It will not
look back a say, "Gee, when I replaced the two spaces with one it left
two spaces because there were originally three. I have to go try
again." It's only going to go through once. If there is a chance you
might have had three spaces somewhere, then you have to run it again.
If you started in the middle of a column, it will run down to the end,
go to the top and come down to the starting point, but it will not
loop. Your friend is mistaken or misunderstood the issue.

 

[Roger Govier]

Hi Steve

I would be very surprised if there were a registry setting that would
change the behaviour.
The behaviour as described is, I believe, what most people would expect.
Try disabling any Addins on the other machine, and see if that makes a
difference. Then add back each one that is currently being used in turn
to see if the behaviour returns.

 

[Dave Peterson]

Another way to disable macros for testing is to open excel in safe mode.

Close excel
windows start button|Run
excel /safe

And do your edit|Replace testing.