Using wild card characters in Find & Replace

T

Tola

Hi,

Below a previous posted problem not solved yet !

Maybe somebody can help !

Thanks,

To explain the problem better:
Have 3 workbooks with multiple sheets which is consolidated into 1 workbook.
The links to the 3 workbooks had to be changed and was done with the "data,
edit links, change source" command but one of the the links was mistakenly
duplicated resulting in the info below. File was saved so can not undo.

Formula:
:='[AAA.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7

Want to change it to:
:='[AAA.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7+'[CCC.xls]Sheet1'!A7


Want to do this for the whole workbook iso doing it one by one since it is
has approx 2000 links that follow the same logic, with only the file name to
be changed.

The rest below might make more sense now !

Tola said:
Hi Luke,

I tried the following:
Find:
+'[BBB.xls]??????????
Replace with:
+'[CCC.xls]??????????

Message: Excel found no data to replace

The formula contains a link to the one file twice and I want to replace one
of the links with another link. Therfor can not use Data,edit links, change
source.

Can do what you suggest but means I will have to do so cell by cell and the
change in the link is required in multiple cells on multiple sheets !


Luke M said:
They are recognized, but you may not be applying them correctly. Also, if
you search for a wildcard, you'd have to define what to replace that
wildcard with! Perhaps you can do:
Find:
+'[BBB.xls]Sheet1'!A7+
Replace with:
+'[CCC.xls]Sheet1'!A7+

--
Best Regards,

Luke M
Tola said:
How do I change a range of Formula linked to 3 other files ?

Example:
Cell Reference:
A7:='[AAA.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7
A8:='[AAA.xls]Sheet1'!A8+'[BBB.xls]Sheet1'!A8+'[BBB.xls]Sheet1'!A8


Want to change one of the references to '[BBB.xls] to '[CCC.xls] using
wildcard characters but * and ? does not seam to be regognized in find and
repalce ?
Click to expand...


.
Click to expand...
Click to expand...
Was this post helpful to you?
 
R

Roger Govier

Hi

Replace
'[BBB.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7

With
'[BBB.xls]Sheet1'!A7+'[CCC.xls]Sheet1'!A7

--
Regards
Roger Govier
Hi,

Below a previous posted problem not solved yet !

Maybe somebody can help !

Thanks,

To explain the problem better:
Have 3 workbooks with multiple sheets which is consolidated into 1 workbook.
The links to the 3 workbooks had to be changed and was done with the "data,
edit links, change source" command but one of the the links was mistakenly
duplicated resulting in the info below. File was saved so can not undo.

Formula:
:='[AAA.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7

Want to change it to:
:='[AAA.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7+'[CCC.xls]Sheet1'!A7


Want to do this for the whole workbook iso doing it one by one since it is
has approx 2000 links that follow the same logic, with only the file name to
be changed.

The rest below might make more sense now !

Tola said:
Hi Luke,

I tried the following:
Find:
+'[BBB.xls]??????????
Replace with:
+'[CCC.xls]??????????

Message: Excel found no data to replace

The formula contains a link to the one file twice and I want to replace one
of the links with another link. Therfor can not use Data,edit links, change
source.

Can do what you suggest but means I will have to do so cell by cell and the
change in the link is required in multiple cells on multiple sheets !


Luke M said:
They are recognized, but you may not be applying them correctly. Also, if
you search for a wildcard, you'd have to define what to replace that
wildcard with! Perhaps you can do:
Find:
+'[BBB.xls]Sheet1'!A7+
Replace with:
+'[CCC.xls]Sheet1'!A7+

--
Best Regards,

Luke M
How do I change a range of Formula linked to 3 other files ?

Example:
Cell Reference:
A7:='[AAA.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7
A8:='[AAA.xls]Sheet1'!A8+'[BBB.xls]Sheet1'!A8+'[BBB.xls]Sheet1'!A8


Want to change one of the references to '[BBB.xls] to '[CCC.xls] using
wildcard characters but * and ? does not seam to be regognized in find and
repalce ?

.
Click to expand...
Click to expand...
Was this post helpful to you?
Click to expand...
 
R

Roger Govier

Sorry - ignore my post.
Didn't read the question properly
--
Regards
Roger Govier

Roger said:
Hi

Replace
'[BBB.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7

With
'[BBB.xls]Sheet1'!A7+'[CCC.xls]Sheet1'!A7

--
Regards
Roger Govier
Hi,

Below a previous posted problem not solved yet !

Maybe somebody can help !

Thanks,

To explain the problem better:
Have 3 workbooks with multiple sheets which is consolidated into 1
workbook.
The links to the 3 workbooks had to be changed and was done with the
"data, edit links, change source" command but one of the the links was
mistakenly duplicated resulting in the info below. File was saved so
can not undo.

Formula:
:='[AAA.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7

Want to change it to:
:='[AAA.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7+'[CCC.xls]Sheet1'!A7


Want to do this for the whole workbook iso doing it one by one since
it is has approx 2000 links that follow the same logic, with only the
file name to be changed.

The rest below might make more sense now !

Tola said:
Hi Luke,

I tried the following:
Find:
+'[BBB.xls]??????????
Replace with:
+'[CCC.xls]??????????

Message: Excel found no data to replace

The formula contains a link to the one file twice and I want to
replace one of the links with another link. Therfor can not use
Data,edit links, change source.

Can do what you suggest but means I will have to do so cell by cell
and the change in the link is required in multiple cells on multiple
sheets !


:

They are recognized, but you may not be applying them correctly.
Also, if you search for a wildcard, you'd have to define what to
replace that wildcard with! Perhaps you can do:
Find:
+'[BBB.xls]Sheet1'!A7+
Replace with:
+'[CCC.xls]Sheet1'!A7+

--
Best Regards,

Luke M
How do I change a range of Formula linked to 3 other files ?

Example:
Cell Reference:
A7:='[AAA.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7
A8:='[AAA.xls]Sheet1'!A8+'[BBB.xls]Sheet1'!A8+'[BBB.xls]Sheet1'!A8


Want to change one of the references to '[BBB.xls] to '[CCC.xls] using
wildcard characters but * and ? does not seam to be regognized in
find and
repalce ?

.
Click to expand...
Was this post helpful to you?
Click to expand...
Click to expand...
 
R

Roger Govier

Hi

Use the following piece of VBA code

Sub ChangeFile()
Dim c As Range
Dim x As Variant
For Each c In Selection
x = Split(c.Formula, "+")
x(2) = Replace(x(2), "BBB", "CCC")
c.Formula = Join(x, "+")
Next
End Sub

To install
Copy code above
Alt+F11 to invoke VB Editor
Alt+I+M to insert Module
Paste code into white pane that appears
Alt+F11 to return to Excel

To Use
Select the column of cells which contain the wrong formula
Alt+F8 to bring up the macro dialogue
select ChangeFile
Run

Repeat for each of the sheets that you need to change

--
Regards
Roger Govier
Hi,

Below a previous posted problem not solved yet !

Maybe somebody can help !

Thanks,

To explain the problem better:
Have 3 workbooks with multiple sheets which is consolidated into 1 workbook.
The links to the 3 workbooks had to be changed and was done with the "data,
edit links, change source" command but one of the the links was mistakenly
duplicated resulting in the info below. File was saved so can not undo.

Formula:
:='[AAA.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7

Want to change it to:
:='[AAA.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7+'[CCC.xls]Sheet1'!A7


Want to do this for the whole workbook iso doing it one by one since it is
has approx 2000 links that follow the same logic, with only the file name to
be changed.

The rest below might make more sense now !

Tola said:
Hi Luke,

I tried the following:
Find:
+'[BBB.xls]??????????
Replace with:
+'[CCC.xls]??????????

Message: Excel found no data to replace

The formula contains a link to the one file twice and I want to replace one
of the links with another link. Therfor can not use Data,edit links, change
source.

Can do what you suggest but means I will have to do so cell by cell and the
change in the link is required in multiple cells on multiple sheets !


Luke M said:
They are recognized, but you may not be applying them correctly. Also, if
you search for a wildcard, you'd have to define what to replace that
wildcard with! Perhaps you can do:
Find:
+'[BBB.xls]Sheet1'!A7+
Replace with:
+'[CCC.xls]Sheet1'!A7+

--
Best Regards,

Luke M
How do I change a range of Formula linked to 3 other files ?

Example:
Cell Reference:
A7:='[AAA.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7
A8:='[AAA.xls]Sheet1'!A8+'[BBB.xls]Sheet1'!A8+'[BBB.xls]Sheet1'!A8


Want to change one of the references to '[BBB.xls] to '[CCC.xls] using
wildcard characters but * and ? does not seam to be regognized in find and
repalce ?

.
Click to expand...
Click to expand...
Was this post helpful to you?
Click to expand...
 
M

Ms-Exl-Learner

Just remove the single quotes from your formula:
='[AAA.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7
='[AAA.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7+'[CCC.xls]Sheet1'!A7

To
=[AAA.xls]Sheet1!A7+[BBB.xls]Sheet1!A7+[BBB.xls]Sheet1!A7
=[AAA.xls]Sheet1!A7+[BBB.xls]Sheet1!A7+[CCC.xls]Sheet1!A7

Excel generally ADD single quotes when the Workbook Name or Sheet Name is
having a space. If there is no space in WORKBOOK NAME or in SHEET NAME then
it won’t add the single quote.

So open your workbook and Press Cntrl+H which will get you the Find and
Replace Dialog Box. In [Find What] copy and paste the below value:

=[AAA.xls]Sheet1!A7+[BBB.xls]Sheet1!A7+[BBB.xls]Sheet1!A7

In [Replace With] field copy and paste the below value:
=[AAA.xls]Sheet1!A7+[BBB.xls]Sheet1!A7+[CCC.xls]Sheet1!A7

Under the [Within] field select WORKBOOK from the Drop Down. Now Click
Replace All.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


Tola said:
Hi,

Below a previous posted problem not solved yet !

Maybe somebody can help !

Thanks,

To explain the problem better:
Have 3 workbooks with multiple sheets which is consolidated into 1 workbook.
The links to the 3 workbooks had to be changed and was done with the "data,
edit links, change source" command but one of the the links was mistakenly
duplicated resulting in the info below. File was saved so can not undo.

Formula:
:='[AAA.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7

Want to change it to:
:='[AAA.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7+'[CCC.xls]Sheet1'!A7


Want to do this for the whole workbook iso doing it one by one since it is
has approx 2000 links that follow the same logic, with only the file name to
be changed.

The rest below might make more sense now !

Tola said:
Hi Luke,

I tried the following:
Find:
+'[BBB.xls]??????????
Replace with:
+'[CCC.xls]??????????

Message: Excel found no data to replace

The formula contains a link to the one file twice and I want to replace one
of the links with another link. Therfor can not use Data,edit links, change
source.

Can do what you suggest but means I will have to do so cell by cell and the
change in the link is required in multiple cells on multiple sheets !


Luke M said:
They are recognized, but you may not be applying them correctly. Also, if
you search for a wildcard, you'd have to define what to replace that
wildcard with! Perhaps you can do:
Find:
+'[BBB.xls]Sheet1'!A7+
Replace with:
+'[CCC.xls]Sheet1'!A7+

--
Best Regards,

Luke M
How do I change a range of Formula linked to 3 other files ?

Example:
Cell Reference:
A7:='[AAA.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7
A8:='[AAA.xls]Sheet1'!A8+'[BBB.xls]Sheet1'!A8+'[BBB.xls]Sheet1'!A8


Want to change one of the references to '[BBB.xls] to '[CCC.xls] using
wildcard characters but * and ? does not seam to be regognized in find and
repalce ?


.
Click to expand...
Click to expand...
Was this post helpful to you?
Click to expand...
 
B

Billy Liddel

Hi Tola

If you want to go with a macro solution this should help.

'=========================
Sub Test()
Dim rng As Range
Dim c As Variant
Dim Frmla As String
Dim count As Integer

'Continue if formula does not contain BBB
On Error Resume Next

For Each c In ActiveSheet.UsedRange
If c.HasFormula Then
Frmla = c.Formula
Frmla = WorksheetFunction.Substitute(Frmla, "BBB", "CCC", 2)
c.Formula = Frmla
End If
Next
End Sub
' ====================================

To use a macro Press ALT + F11, choose Insert, Module and paste the code
between the lines into the module. Press ALT + Q to return to the workbook.

Selecting the sheet with the formulas press ALT + F8, select the macro and
click Run.

HTH
Peter
Tola said:
Hi,

Below a previous posted problem not solved yet !

Maybe somebody can help !

Thanks,

To explain the problem better:
Have 3 workbooks with multiple sheets which is consolidated into 1 workbook.
The links to the 3 workbooks had to be changed and was done with the "data,
edit links, change source" command but one of the the links was mistakenly
duplicated resulting in the info below. File was saved so can not undo.

Formula:
:='[AAA.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7

Want to change it to:
:='[AAA.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7+'[CCC.xls]Sheet1'!A7


Want to do this for the whole workbook iso doing it one by one since it is
has approx 2000 links that follow the same logic, with only the file name to
be changed.

The rest below might make more sense now !

Tola said:
Hi Luke,

I tried the following:
Find:
+'[BBB.xls]??????????
Replace with:
+'[CCC.xls]??????????

Message: Excel found no data to replace

The formula contains a link to the one file twice and I want to replace one
of the links with another link. Therfor can not use Data,edit links, change
source.

Can do what you suggest but means I will have to do so cell by cell and the
change in the link is required in multiple cells on multiple sheets !


Luke M said:
They are recognized, but you may not be applying them correctly. Also, if
you search for a wildcard, you'd have to define what to replace that
wildcard with! Perhaps you can do:
Find:
+'[BBB.xls]Sheet1'!A7+
Replace with:
+'[CCC.xls]Sheet1'!A7+

--
Best Regards,

Luke M
How do I change a range of Formula linked to 3 other files ?

Example:
Cell Reference:
A7:='[AAA.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7
A8:='[AAA.xls]Sheet1'!A8+'[BBB.xls]Sheet1'!A8+'[BBB.xls]Sheet1'!A8


Want to change one of the references to '[BBB.xls] to '[CCC.xls] using
wildcard characters but * and ? does not seam to be regognized in find and
repalce ?


.
Click to expand...
Click to expand...
Was this post helpful to you?
Click to expand...
 
B

Billy Liddel

Tola

I've added a couple of vital lnes to the code to stop excel trying to update
the changed links. Use this code please.

Sub Test()
Dim rng As Range
Dim c As Variant
Dim Frmla As String
Dim count As Integer

'prevent updating
With Application
.Calculation = xlManual
.DisplayAlerts = False
End With
'Continue if formula does not contain BBB
On Error Resume Next

For Each c In ActiveSheet.UsedRange
If c.HasFormula Then
Frmla = c.Formula
Frmla = WorksheetFunction.Substitute(Frmla, "BBB", "CCC", 2)
c.Formula = Frmla
End If
Next
With Application
.Calculation = xlAutomatic
End With
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using wildcard characters in find & replace 3
Finding identical rows in a list 6
paste data in different sheet but skip rows. 2
Macro loop opening files in folder successivly 5
Is there a way to set a cell value to be constant in formulas? 4
Withusing of input box data store in given cell 11
COUNTIF Multiple Conditions 5
How to add days in date by macro 4

Top