View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
Tom Ogilvy Tom Ogilvy is offline
external usenet poster
 
Posts: 27,285
Default Using substitute to replace text that varies

If this is a programming question

activeCell.Value = Application.Substitute(ActiveCell.Value,"X","Y")

or if using xl2000 or later (use the replace function)
activeCell.Value = Replace(ActiveCell.Value,"X","Y")

--
Regards,
Tom Ogilvy



"HJ" wrote in message
om...
I'd like some assistance with using substitute. I need to look for
any phrase, for example:

X123X and replace the X with Y so that it becomes Y123Y
or
XAB1278X and replace the X with Y to become YAB1278Y

As you can see... the number of characters between the two X's can
vary. Is substitute able to have 'wildcard' symbols to denote that it
needs to look for any number of text before the 2nd X?

I cannot do a blanket search/replace for X to be replaced with Y as it
will affect other things.

If substitute isn't the right command to use... what would you
suggest?

Hope what I'm asking for is clear...

Thanks