Multiple Replace

  • Thread starter Thread starter Davoud
  • Start date Start date
D

Davoud

Is it possible to replace multiple values within a string simultaneously?

eg. I have over 600 records of links to external workbooks where each fiscal
period I need to change the Period #

='F:\VC FPBS\FY07-08\Performance Analysis\Community\P07\[VC Consolidated
Report P7 Sandra.xls]Pd7'!AJ14

Where I want to change "P07", "P7" and "Pd7" to "P08","P8" and "Pd8".

Obviously I can't just replace "7" with "8" because I would lose the
directory reference "FY07-08". Is there a way to parse the replace request so
that I can replace all three values at once?

Thanks in advance for any assistance.
 
Can you change the whole string at once--include everything in the From: and
everything in the To: strings.

I'd copy from the formulabar and paste into both boxes in the Edit|Replace
dialog. Then fix the To: string to what I needed.
Is it possible to replace multiple values within a string simultaneously?

eg. I have over 600 records of links to external workbooks where each fiscal
period I need to change the Period #

='F:\VC FPBS\FY07-08\Performance Analysis\Community\P07\[VC Consolidated
Report P7 Sandra.xls]Pd7'!AJ14

Where I want to change "P07", "P7" and "Pd7" to "P08","P8" and "Pd8".

Obviously I can't just replace "7" with "8" because I would lose the
directory reference "FY07-08". Is there a way to parse the replace request so
that I can replace all three values at once?

Thanks in advance for any assistance.
Click to expand...
 
Try this
Sub mychange()
For Each mycell In Range("A1:A3")
mycell.Replace What:="P7", Replacement:="P8"
mycell.Replace What:="P07", Replacement:="P08"
mycell.Replace What:="Pd7", Replacement:="Pd8"
Next
End Sub
 
Thanks! That did the trick.

Dave Peterson said:
Can you change the whole string at once--include everything in the From: and
everything in the To: strings.

I'd copy from the formulabar and paste into both boxes in the Edit|Replace
dialog. Then fix the To: string to what I needed.
Is it possible to replace multiple values within a string simultaneously?

eg. I have over 600 records of links to external workbooks where each fiscal
period I need to change the Period #

='F:\VC FPBS\FY07-08\Performance Analysis\Community\P07\[VC Consolidated
Report P7 Sandra.xls]Pd7'!AJ14

Where I want to change "P07", "P7" and "Pd7" to "P08","P8" and "Pd8".

Obviously I can't just replace "7" with "8" because I would lose the
directory reference "FY07-08". Is there a way to parse the replace request so
that I can replace all three values at once?

Thanks in advance for any assistance.
Click to expand...
Click to expand...
 
Back
Top