Removing a space in two different spots in a formula

[Virgil Beach]

I have the following formula:
='C:\Documents and Settings\...\[trend (1).xls]trend (1)'! I have 50 worksheets I am linking to so I am having to change them as follows:

[trend (2).xls]trend (2)'! through [trend (47).xls]trend (47)'!

I am having to use the 'Ctrl +"F"' and replace option one by one per each worksheet (50 total worksheets per each workbook):
='C:\Documents and Settings\...\[trend(1).xls]trend(1)'!

removing the space between "trend (" in both spots.

Is there a faster way to replace all spaces in the entire workbook instead of one by one per each worksheet.

This includes counting up per the next worksheet. trend(1), trend(2), trend(3), ... trend(47).

[FDibbins]

trying to understand here. so you want
='C:\Documents and Settings\...\[trend (1).xls]trend (1)'!
to be
='C:\Documents and Settings\...\[trend(1).xls]trend(1)'!
?

or are you trying to change
='C:\Documents and Settings\...\[trend (1).xls]trend (1)'!
to
='C:\Documents and Settings\...\[trend (2).xls]trend (2)'!

[Virgil Beach]

Replacing trend (1) with trend(1) twice in the same command.

then go to the next worksheet in the workbook.

and replacing trend (2) with trend(2).

etc..

trend (47) with trend(47)

[FDibbins]

without knowing what the rest of your formula is, here's a suggestion to play around with.

replace your entire formula with something like this. i didnt put the full path in, that wont work for me unless i have that file, so play around with adding that into your formula and copy it down...

=INDIRECT("'Sheet"&ROW(B2)&"'!"&"A"&ROW($A$1))

[Virgil Beach]

Thanks when I receive more data to update my files I will try this and send you an example sheet.

[shg]

Ctrl+F, click Options, Within: Workbook, replace trend ( with trend(, Replace All

[Virgil Beach]

I have been using the 'Ctrl' + "F" function and replace. However it is a link to another worksheet. so if I try to just replace the space it will break the link.
I think the best way to do this is to remove the "=" from the commmand line =C://.../ and then replace the 2 spaces from the string and then add the "=" back in after ther spaces are removed..

I will try both this option and FDibbins =INDIRECT("'Sheet"&ROW(B2)&"'!"&"A"&ROW($A$1)) and let everyone know how it goes.

Thanks!!!