find text direct in formulas

[Icke197]

Hi community,

the last few weeks I've again and again ressources errors from Excel. To find the ressouces killer I'd like to build a short list of all used formulas that shows the number of e.g. "=lookup" or "=aggregat" or others.

How can I search for a text string directly inside the formula and sum it up and not search in the cells value?

Thanks a lot in before,

Thomas

[#2 · Accepted solution]

@Icke197 

=COUNT(SEARCH("*"&D16&"*",C5:C13))

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021. This formula is in cell C16 in the example.

=FORMULATEXT(B5)

This is the formula in cell C5 which is filled down to cell C13.

[#3]

Hi there and good evening,

You made my day :smiling_face_with_smiling_eyes:! It works perfectly. Thanks for your prompt help.
So I can build up the worksheet completely tomorrow :thumbs_up::ok_hand:.

Thanks again & have a nice evening :smiling_face_with_smiling_eyes:.
Thomas

[#4]

Is there another possibility without reserving for each formula cell another cell with the formulatext method?

Thx

[#5]

@Icke197 

=COUNT(SEARCH("sverweis",FORMULATEXT(C8:C10)))

This works in my sheet. i have to enter the formula with ctrl+shift+enter because i don't work with Office365 or Excel 2021.

 

[#6]

Hi@OliverScheurich ,

works perfect. Now I can build the formula survey quite easily.

THANKS A LOT!!! :)

Best regards,

Thomas