Find and Replace

H

Hari

Hi,

I have Excel 2002 (with Win 2000 ).

1. Lets say I have 10 cells with the text -> Apple in it. Suppose I choose 6
of these cells which has apple in it and then use the Find feature and type
Apple and use Find All feature then I get a message in message bar at the
bottom of Find saying 6 cells found.

2. Now, I close the find window.I select the 6 cells with Apple again and
now use the replace feature. In "Find what" I type Apple and in "Replace
with" I type Orange and then press Find all, I get the message saying 6
cell(s) found. Now, I press Replace all and the message says "Excel has
completed its search and made 10 replacements". My question is I had
selected only 6 cells and find ( Find All ) also displayed the same but in
using replace option it replaces all the 10 cells?

Futher lead :- If after step 1 I dont do follow step 2 given above but
follow an alternative step 2 outlined below then :-
Alternative step 2 - Now without closing the help window I navigate to the
replace tab in Find window. In "Find what" I type Apple and in "Replace
with" I type Orange and then press . Result same as step 2 above.

Now, I do undo the steps performed above and select the 6 cells with apple
and In "Find what" I type Apple and in "Replace with" I type Orange. Then I
press on Replace all and this time excel Replaces only the selected 6 cells.

Why is it that if we use Find all feature just before before Replace all
then all the cells in worksheet having the find what value gets replaced.
But if we use Replace all directly then only the desired cells get replaced.

I notice that when we select cells and do an operation Find all then after
displaying the results of find operation the cells which we had selected or
no longer in "selection mode". ( I understand that this might be why when we
use replace subsequently all gets replaced). On the contary when we do
select our cells and do a Replace all then after doing the replace operation
the cells which we had selected are STILL in "selection mode". Whats the
logic of the same. IMHO it should be the other way around because after
doing a replace all one might not do any find/replace operation on the
selected region at that time itself so if the target cells are there in
selection mode or not doesnt matter. While after doing a find all one could
very well go for replace all on the originally selected cells.

Regards,
Hari
India
 
D

Dave Peterson

Take a look at your worksheet right after you do a Find All.

You'll see that excel changed your selection from all 6 cells to the first one
it found in that range.

So you only have one cell selected. Now when you do "replace all", excel only
sees on cell selected and figures you mean all the cells. (Just like normal.)

So if you really want to use "Find All" before your "replace all", you'll have
to reselect that range.

Or just stop doing the "Find all" before the "replace all".
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find & Replace 2
Find and Replace 4
Find / Replace 3
Use Find and Replace in Excel 2
FIND&REPLACE 2
Find/Replace Text In Cells 1
find part text, replace all text 4
Find and Replace Lookup errors 7

Top