Help with Find/Replace

henrytm82

New Member
Joined
Apr 8, 2013
Messages
12
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?
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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

Well-known Member
Joined
Feb 26, 2002
Messages
2,318
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,561
Messages
5,596,849
Members
414,107
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top