Help with Find/Replace

[henrytm82]

I have a spreadsheet that's used to track biological data, so there is a lot of repetition. Certain species of plants, for example, share the same Genus name as another species, and each of them is assigned a Species Number. The relevant columns look like this:

SP# | Genus | Species
002 | androp | gerar
003 | androp | scopa


My issue is that there have been changes among biologists as to the classifications of these plants, so now 003 is no longer in the "androp" genus, it now belongs to the "schiza" genus. But I can't just do a straight-up find/replace to make all instances of "androp" into "schiza" because 002 needs to stay "androp".

So my question is how can I find and replace only the instances of a name that I want to replace, and keep those that I don't? I imagine some sort of column-dependent query (something like "only replace "androp" with "schiza" if it is preceded by "003") but I haven't a clue how to do that in Excel.

Does anyone have any ideas to make my life easier than going through over 4300 rows of data by hand?

 

[HalfAce]

Assuming this is pretty much a one time shot (at least for each change like this you want to make), here's what I did that seems to have worked for me.
Do an autofilter on the column of 002, 003, (etc.). I filtered on 003 since that's the plant you want to change.
Next, I highlighted the column with the values 'androp' and used Edit > Replace. I then replaced 'androp' with 'schiza'.
When I turned off the AutoFilter, only the rows with 003 in the SP column got changed to schiza in the Genus column.
(All the 002 rows remained as androp in the Genus column).

Hope it helps.

 

[Asala42]

There is a trick for this kind of thing.

- Apply an autofilter such that only the records you want to change are visible
- Select the range
- CTRL+G (will open a "Go To" window)
- Hit "Special"
- Check "Visible Cells Only"

Then you can do an edit/replace on those cells only

 

[henrytm82]

Thanks guys, this helped

 

[HalfAce]

Most welcome. Glad it helped.