Deleting part of a formula but preserving the rest

[JWill78]

Hi

I have a worksheet which has formulae which reference multiple other worksheets, e.g.

[TABLE="width: 800"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Sheet 1'!W367+'Sheet 2'!W367+'Sheet 3'!W367[/TD]
[TD]Sheet 1'!Z367+'Sheet 2'!Z367+'Sheet 3'!Z367[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Sheet 1'!W370+'Sheet 2'!W370+'Sheet 3'!W370[/TD]
[TD]Sheet 1'!Z370+'Sheet 2'!Z370+'Sheet 3'!Z370[/TD]
[/TR]
</tbody>[/TABLE]

My question is is there a quick way of deleting all references in the above formulas to Sheet2 (or indeed Sheet 1 or 3) whilst preserving the rest of the formula?

Many thanks in advance

John

 

[Oscar Cronquist]

Yes, there is.

Press CTRL + H to open "Find & Replace"

Find what: 'Sheet 1'!
Replace: (empty)

Click "Replace All" button.

 

[Yongle]

Select cells
Use Find & Replace

Find what:
'Sheet 2'!

Replace With:
(leave blank)

Replace ALL

 

[Peter_SSs]

For that particular example, you could select those 2 columns and then do a Find/Replace with
Find what: +'Sheet 2'!*+
Replace with: +
In Options, make sure 'Match entire cell contents' is not checked
Replace All

 

[JWill78]

That's great thanks!