Edit Replace command

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

smxdev@smx21.com - 29 Jun 2007 16:53 GMT

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

Reply to this Message

Rick Rothstein (MVP - VB) - 29 Jun 2007 18:18 GMT

>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
[quoted text clipped - 4 lines]
> 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

Reply to this Message

SteveT - 30 Jun 2007 02:34 GMT

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

> >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
[quoted text clipped - 15 lines]
>
> Rick

Reply to this Message

Dave Peterson - 30 Jun 2007 04:54 GMT

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???????

> I don't think you read my post.
>
[quoted text clipped - 46 lines]
> >
> > Rick

Signature

Dave Peterson

Reply to this Message

Roger Govier - 30 Jun 2007 08:54 GMT

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.

Signature

Regards

Roger Govier

>I don't think you read my post.
>
[quoted text clipped - 65 lines]
>>
>> Rick

Reply to this Message

SteveT - 02 Jul 2007 01:11 GMT

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

Reply to this Message

Roger Govier - 02 Jul 2007 10:21 GMT

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.

Signature

Regards

Roger Govier

> Thanks for both responses. I mentioned 30,000 rows because that is an
> actual
[quoted text clipped - 12 lines]
> Thanks for your assistance,
> Steve

Reply to this Message

Dave Peterson - 02 Jul 2007 12:06 GMT

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.

> Thanks for both responses. I mentioned 30,000 rows because that is an actual
> file we are using.
[quoted text clipped - 9 lines]
> Thanks for your assistance,
> Steve

Signature

Dave Peterson

Reply to this Message

It's the Principle! - 02 Jul 2007 02:01 GMT

<smxdev@smx21.com> wrote in microsoft.public.excel:

> I have someone who's Replace command replaces everything in one
> pass. With mine however I have to repeat the process. This is
[quoted text clipped - 9 lines]
>
> 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.

Signature

Brandy Alexandre

The measure of a man's real character is what he would do if he knew he
never would be found out. -- Thomas Babington Macaulay

Reply to this Message