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.

	A	B
1	Sheet 1'!W367+'Sheet 2'!W367+'Sheet 3'!W367	Sheet 1'!Z367+'Sheet 2'!Z367+'Sheet 3'!Z367
2	Sheet 1'!W370+'Sheet 2'!W370+'Sheet 3'!W370	Sheet 1'!Z370+'Sheet 2'!Z370+'Sheet 3'!Z370

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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!