Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using substitute to replace text that varies
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using substitute to replace text that varies
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 -- schuurke28 ------------------------------------------------------------------------ schuurke28's Profile: http://www.excelforum.com/member.php...o&userid=15818 View this thread: http://www.excelforum.com/showthread...hreadid=273154 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using substitute to replace text that varies
Thanks for the help with using substitute. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Keep text matching a wildcard spec in replace/substitute | Excel Worksheet Functions | |||
Substitute/Replace | Charts and Charting in Excel | |||
Replace or Substitute for COMBIN function | Excel Discussion (Misc queries) | |||
merged cells into one text cell, size varies dependant on text dat | Excel Discussion (Misc queries) | |||
Substitute ,replace and delete in a cell. | Excel Worksheet Functions |