A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Find & Replace - Limit search to a specific column



 
 
Thread Tools Display Modes
  #1  
Old July 27th 08, 01:58 PM posted to microsoft.public.excel.worksheet.functions
falena23
external usenet poster
 
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
Ads
  #2  
Old July 27th 08, 05:40 PM posted to microsoft.public.excel.worksheet.functions
Wigi
external usenet poster
 
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  
Old July 28th 08, 02:26 AM posted to microsoft.public.excel.worksheet.functions
falena23
external usenet poster
 
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  
Old July 28th 08, 03:46 PM posted to microsoft.public.excel.worksheet.functions
Spiky
external usenet poster
 
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.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
FIND SPECIFIC TEXT AND REPLACE IT Tree Excel Worksheet Functions 5 March 27th 08 01:27 AM
Limit to string length in search@replace? 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 01:18 AM


All times are GMT +1. The time now is 05:52 AM.


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