Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find & Replace - Limit search to a specific column
I've recently switched from Excel 2000 to Excel 2007. I understood that one
could limit a Find / Replace to a column by selecting that column before CTRL + H. This works if you select Replace All - but if you select Find All then Replace All - then the replace will take place in all columns. You can see why this is - the focus in the spreadsheet goes from the column to the first cell found. Nowhere in HELP is there any information on this. Also checking a couple of Excel tips sites they suggest selecting the column first but do not warn about the anomaly above. As I'm working on over a 100,000 rows I find it more than a nuisance. Any suggestions on how you can keep Find/Replace working in a single column will be much appreciated |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find & Replace - Limit search to a specific column
A line of VBA can do it:
for intance: Columns("A").Replace What:=1, Replacement:=2, SearchOrder:=xlByColumns Wigi -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "falena23" wrote: I've recently switched from Excel 2000 to Excel 2007. I understood that one could limit a Find / Replace to a column by selecting that column before CTRL + H. This works if you select Replace All - but if you select Find All then Replace All - then the replace will take place in all columns. You can see why this is - the focus in the spreadsheet goes from the column to the first cell found. Nowhere in HELP is there any information on this. Also checking a couple of Excel tips sites they suggest selecting the column first but do not warn about the anomaly above. As I'm working on over a 100,000 rows I find it more than a nuisance. Any suggestions on how you can keep Find/Replace working in a single column will be much appreciated |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find & Replace - Limit search to a specific column
Hi Wigi,
Thanks for the response . The VBA code has the same effect as running REPLACE all in FIND/REPLACE. I did not explain myself well . I wish to inspect the result of a find in the FIND/REPLACE window before I do any replace and perhaps only selectively replace. The FIND/REPLACE window is misleading because it returns only hits in the selected column but if you do anything other than a REPLACE ALL immediately after the FIND it can find and or replace cells that were not in the originally selected column. "Wigi" wrote: A line of VBA can do it: for intance: Columns("A").Replace What:=1, Replacement:=2, SearchOrder:=xlByColumns Wigi -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "falena23" wrote: I've recently switched from Excel 2000 to Excel 2007. I understood that one could limit a Find / Replace to a column by selecting that column before CTRL + H. This works if you select Replace All - but if you select Find All then Replace All - then the replace will take place in all columns. You can see why this is - the focus in the spreadsheet goes from the column to the first cell found. Nowhere in HELP is there any information on this. Also checking a couple of Excel tips sites they suggest selecting the column first but do not warn about the anomaly above. As I'm working on over a 100,000 rows I find it more than a nuisance. Any suggestions on how you can keep Find/Replace working in a single column will be much appreciated |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find & Replace - Limit search to a specific column
It's the Find All that does this. Try using Find Next instead to
scroll through and check all the instances. Then Replace should still work with your original selection. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FIND SPECIFIC TEXT AND REPLACE IT | Excel Worksheet Functions | |||
Limit to string length in search@replace? | Excel Discussion (Misc queries) | |||
Find & replace in one column | Excel Discussion (Misc queries) | |||
search column of text cellto identify those cells with specific w | Excel Worksheet Functions | |||
Find/replace search field defaults | Excel Discussion (Misc queries) |