Confine Find and Replace

[Alan C]

Can Find and Replace be confined to a single column without finding all instance of the subject in the entire sheet? In the problem column I have Cities and States. I want to find all the states and replace with nothing. There are other columns with states in them and I don't want them affected. Any help will be appreciated.

Thanks,

Alan

 

[Steve=True]

yes, just select the column or data range for the Find and Replace

 

[stnkynts]

Like Steve said or a macro can easily be created to search a specific range for values and then replace. Since you mention states is it going to be all 50 states or just a couple? What column is the "states" in. Do you want to replace with the word "nothing" or clear the cells contents? More information is needed for that though.

 

[Alan C]

Steve:

I don't think that works, because it returns many more instances in number than the column contains.

Alan

 

[Steve=True]

Alan, it worked for me. I just highlighted the column, then did the find/replace.

Just tested it again and it only changed the column or range that was selected.

Create a test sheet with your data and give it a shot.

maybe you have more instances than you thought.

 

[Alan C]

Stnkynts:

Thanks for the response. It's one state that has to be removed and replaced with no state name. I thought that by selecting the column, the search would be confined to that one column, but it keeps returning many more than is contained in the selected cells and that is why I think this is counting in additional areas.

Thanks all,

Alan

 

[Alan C]

OK Steve,
I'll give it a try, and thanks.

 

[Burrgogi]

Can Find and Replace be confined to a single column without finding all instance of the subject in the entire sheet? In the problem column I have Cities and States. I want to find all the states and replace with nothing. There are other columns with states in them and I don't want them affected. Any help will be appreciated.

Thanks,

Alan

Alan,

You can use a function or a VBA solution but before either solution can be posted, it would be helpful to know
#1) which col. you want to perform the find & replace
#2) what the actual data looks like.

e.g.
Chicago, IL
Chicago IL
Chicago, Illinois
Chicago Illinois

Why don't you post some more details? You will get better answers that way.