View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Katy Katy is offline
external usenet poster
 
Posts: 23
Default Find & Replace: find part cell, replace whole cell

I would like to be able to do a Find & Replace where I find any cell which
contains a certain text string within the cell contents, and replaces the
WHOLE CELL with another text string. Anyone know of a simple way to do this?

Specifically, I have a large number of cells with contents like
01/06/2008 - 30/06/2008
The first date is always the same but the second date can be any one of
hundreds of dates. I want to find all cells which start "01/06/2008" and
replace the whole cell with "2008-09". It seems that if I do a Find &
Replace (ctrl-H) without 'match entire cell contents' selected, then only the
first part of the cell is replaced and the second date remains (not what I
want), but to get a 'match entire cell contents' to work, I would have to do
a separate Find & Replace for every possible original value of the cell
(hundreds of possibilities).

I'm not great with VBA, but I tried recording a macro while I did the Find &
Replace, to see if I could then amend the code to make it work, but it didn't
look easy to do it this way so I think I need an entirely new bit of code!
Any ideas...?

Thanks
Katy