SOLVED

New Contributor

# find text direct in formulas

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

5 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: find text direct in formulas

``=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.

# Re: find text direct in formulas

Hi there and good evening,

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

Thanks again & have a nice evening .
Thomas

# Re: find text direct in formulas

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

Thx

# Re: find text direct in formulas

``=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.

# Re: find text direct in formulas

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

THANKS A LOT!!!

Best regards,

Thomas