SOLVED

find text direct in formulas

Copper Contributor

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

@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.

text string in formula.JPG

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
Is there another possibility without reserving for each formula cell another cell with the formulatext method?

Thx

@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.

text in formulas.JPG 

Hi@OliverScheurich ,

 

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

THANKS A LOT!!! :)

 

Best regards,

Thomas

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
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.

text string in formula.JPG

View solution in original post