Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 27th 08, 01:58 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2008
Posts: 2
Default 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   Report Post  
Old July 27th 08, 05:40 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 396
Default 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   Report Post  
Old July 28th 08, 02:26 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2008
Posts: 2
Default 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   Report Post  
Old July 28th 08, 03:46 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2008
Posts: 622
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
FIND SPECIFIC TEXT AND REPLACE IT Tree Excel Worksheet Functions 5 March 27th 08 12:27 AM
Limit to string length in [email protected]? Richard Fangnail Excel Discussion (Misc queries) 0 August 14th 07 06:22 PM
Find & replace in one column Pete Excel Discussion (Misc queries) 4 May 2nd 07 02:23 AM
search column of text cellto identify those cells with specific w Ross Headifen Excel Worksheet Functions 1 July 8th 06 02:49 PM
Find/replace search field defaults Kristin Excel Discussion (Misc queries) 0 November 18th 05 12:18 AM


All times are GMT +1. The time now is 09:59 AM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017