Bulk Change existing formulas on sheet

[oblix]

I have a sheet that has formulas linking to other sheet cells
example of one cell: ='Sheet 1'!G18
would like to change all formulas on a specified sheet to a text formula like example below:
=TEXT('Sheet 1'!G18,"$0.00")
there are just to many to change every single formula to a text formula

is this possible with vba code?

 

[etaf]

would a find a replace work - i have used a few times to change formulas
only issue is if 'sheet1'!G18 appears where you do not want to change

find: 'Sheet 1'!G18
Replace: TEXT('Sheet 1'!G18,"$0.00")

 

[oblix]

I tried but maybe I did it wrong
can you give me example from above examples

 

[etaf]

so i put
=Sheet1!G15
into A1 and then used find a replace
and changed it

But it does depend on whats in other cells

Screenshot 2022-10-22 at 15.01.13.png

Shared with Dropbox

www.dropbox.com

 

[oblix]

Thank you undestand that, but each cell has a different formula(link to other cells).
Thats the part I dont understand how to replace where each cell content is different

 

[etaf]

can you give examples of the different cells
you originally said

example of one cell: ='Sheet 1'!G18
would like to change all formulas on a specified sheet to a text formula like example below:
=TEXT('Sheet 1'!G18,"$0.00")

so more examples would help here , as i had assumed the G18 was consistant
If it could be any formula with any text in, then i dont know how to change that - - so maybe a VBA solution is your only choice - not my area sorry

 

[oblix]

The only thing that will be the same in all cells will be the = of the formula, as each formula referes to different sheet and different cells
cant a place holder be given for existing text to be used in the find ?

 

[oblix]

did it all manually
Issue resolved

 

[etaf]

thanks for the update

 

[smozgur]

Solving it manually is one way. However, to make it less painful, here is the VBA code to do that.
Note: Requires selecting the cells since there is no specific pattern in formulas.

Sub changeFunctions()
Dim rng As Range
Dim cll As Range
    Set rng = Selection
    
    For Each cll In rng.Cells
        If cll.HasFormula Then 'just in case if there are value cells in the selection
            cll.Formula = "=TEXT(" & Right(cll.Formula, Len(cll.Formula) - 1) & ", ""$0.00"")"
        End If
    Next cll
End Sub