Without VBA replace all only the match string

[dluhut]

Hello,

In the formula bar where the formula is ......\10 - October 2016\... ... ...\Oct 2016\....

How do I do a replace where I can just change the above to ......\11 - November 2016\... ... ...\Nov 2016\....

Where everything stays the same?

I tried using \11 - November 2016\ * \Nov 2016\ and didn't work, anything that's in the middle is changed to the asterisk.

Edit: For more clarification, assume that you have the following formula.

Please Login or Register  to view this content.

And you want to change it to

Please Login or Register  to view this content.

Because I want to keep everything all the same, EXCEPT the 10 - October 2016\... ... ...\[Name of the file Oct 2016.xlsx], so I highlight the range, and with Find and Replace, I put

"\10 - October 2016\folder\[Name of the file Oct 2016.xlsx]" on Find What and
"\11 - November 2016\folder\[Name of the file Nov 2016.xlsx]" on Replace With.

Thus am wondering if there's a way in Excel, without using VBA, to be able to do using wild card like below and still able to retain. Example below.

"\10 - October 2016\*Oct 2016.xlsx]" on Find What and
"\11 - November 2016\*Nov 2016.xlsx]" on Replace With.

[TMS]

I'd do it in two passes. 10 - October 2016 to 11 - November 2016 and then Oct 2016 to Nov 2016.

[Pete_UK]

Are those meant to be text values which look like dates, or actual date fields?

It they are text values then you could use Find & replace twice, the first time to replace "October" with "November" and the second time to replace "Oct" with "Nov" (obviously, without the quotes).

If they are actual dates, then you can enter the value 31 in a blank cell somewhere, select that cell and click < copy >, then move the cursor to select the cells with the dates in them, then right-click and choose Paste Special, then click against Add and against Values then OK then press the < Esc > key.

Hope this helps.

Pete

[dluhut]

I'd do it in two passes. 10 - October 2016 to 11 - November 2016 and then Oct 2016 to Nov 2016.

Hi TMS,

Thought of doing that too, but because what I'm trying to replace is an actual formula, and that it's actually has to do with external link to closed workbook, if I were to do it one by one, an annoying 'edit link' window that ask to reference to the correct workbook keeps coming up.

That's why I'm doing the 'replace all' all at once.

[JohnTopley]

Try this

in A2


=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"2016\10",B1),"October",C1),"Oct",D1)

A1= "October" formula

Please Login or Register  to view this content.

B1="2016\11"

C1="November"

D1="Nov"

all above without the quotes!

[JohnTopley]

Or more generic

in B1:C2

2016\10 October Oct
2016\11 November Nov

in A2

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,$B$1,$B$2),$C$1,$C$2),$D$1,$D$2)

[dluhut]

Hi John,

I can't use a substitute or replace formula, because what I'm trying to replace itself is a formula.

Think of it this way, those formula that I have is in cell A1 and it's going all the way down to say cell A10.

What I'm trying to find and replace all is in cell range A1:A10 where they're an actual formula (that's why it started with an equal) and change any string that 'match' and still keep the others the same as is.

[JohnTopley]

Then VBA is probably the only way.

[Pete_UK]

It makes more sense now that you have given better examples of what you have. You can do it in 4 passes of Find & Replace. Select all the cells with the formulae in (A1:A10 ?), then CTRL-H, and:

Find What: =
Replace With: $=
Click Replace All

Find What: October
Replace With: November
Click Replace All

Find What: Oct
Replace With: Nov
Click Replace All

Find What: $=
Replace With: =
Click Replace All

Then you can close the dialogue box. The first changes the formula to a text string so that you can do the two passes on the month and the last one returns it to a formula.

Hope this helps.

Pete