Find within a limited range

s_ojha

Board Regular
Joined
Oct 19, 2004
Messages
92
1. I have 5000 rows record in one column . I want that my FIND & REPLACE to work in the specified range only . When I try to find or replace someting it searches all the worksheet . Always selecting the range and then do the FIND & REPLACE is tireless . Please tell me a way where I can fix the range and when I find records it searches in the specified range only .

Next Queston .
2. I have 1000 row records . For eg. suppose in Column C one row contains Diploma, the other row Overmanship and other row "another word" . I want a formula which could recognise the word "Diploma" or "Overmanship" or another word and put marks as 4 for Diploma, 5 for Overmanship and 6 for any other word in next adjoining column .

Can any body please help .


One question which is out way is how can I delete my post, the answer of which has already been received .
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

headtoadie

Board Regular
Joined
Aug 1, 2003
Messages
219
1) by default the search & replace function works on the whole spreadsheet. You have to highlight a specific area if that is the only area you want impacted. However, since you are limiting your search to a single column you can just click the the column label and highlight the entire column, there is no need to only select the used range.

An alternative is to write a macro that will do the highlighting and search & replace automatically. If you have never written one before the macro recorder is a good place to start. It can be found on the menu at Tools->Macro->Record New Macro...Basically after you start the recording you go through the process that you want to macro to emulate.


2) As for the other item you can use IF statements to evaluate the words and return the value desired. This example assumes that the word to be evaluated is in cell C3

=IF(C3="Diploma",4,IF(C3="Overmanship",3,IF(C3="Another word",2,1)))


HT
 

s_ojha

Board Regular
Joined
Oct 19, 2004
Messages
92
Sir,
I have tried the macro, but it does not fully work . In the macro while recording when i open the find window, there the macro could not be stopped . Could you please write the macro and send it to me . I want to repeat here that suppose I select the whole column the search will search many names and I want to search in limited range only .

The second formulla has worked wonderfully .

Please also tell me about how to delete a post .
 

Forum statistics

Threads
1,147,694
Messages
5,742,671
Members
423,746
Latest member
Joaogomes

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