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: find part cell, replace whole cell



 
 
Thread Tools Display Modes
  #1  
Old June 17th 08, 11:52 PM posted to microsoft.public.excel.worksheet.functions
Katy
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
Ads
  #2  
Old June 18th 08, 12:10 AM posted to microsoft.public.excel.worksheet.functions
Katy
external usenet poster
 
Posts: 23
Default Find & Replace: find part cell, replace whole cell

D'oh - all I needed was a wildcard (*):
Find "01/06/2008*" and replace with "2008-09"
Apologies for wasting people's time! : )

"katy" wrote:

> 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

  #3  
Old June 19th 08, 01:39 PM posted to microsoft.public.excel.worksheet.functions
radicaltp
external usenet poster
 
Posts: 1
Default Find & Replace: find part cell, replace whole cell

beautiful . love u katy

"katy" wrote:

> D'oh - all I needed was a wildcard (*):
> Find "01/06/2008*" and replace with "2008-09"
> Apologies for wasting people's time! : )
>
> "katy" wrote:
>
> > 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

 




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 replace the 1st character in a cell Pete Excel Discussion (Misc queries) 2 March 31st 08 07:27 AM
Find/Replace changes cell format ?????? Woland99 Excel Discussion (Misc queries) 2 January 23rd 08 10:07 AM
find text in cell and replace it with part of the text in that ce. jules Excel Discussion (Misc queries) 3 May 3rd 07 10:18 AM
Find and replace part of a text string [email protected] Excel Discussion (Misc queries) 2 July 10th 06 10:34 PM
How do I find and replace part of a hyperlink that isn't visible? Dede Excel Discussion (Misc queries) 1 April 28th 05 12:15 AM


All times are GMT +1. The time now is 05:29 PM.


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