Using substitute to replace text that varies

[HJ]

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

 

[schuurke28]

substitute is the right command but you must ommit the last parameter
'Instance_num'

i.e.:

=SUBSTITUTE(A1;"X";"Y")

This will replace all X by Y in the text

 

[Tom Ogilvy]

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")

 

[Helen Jenkins]

Thanks for the help with using substitute.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!