• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Finding a specific number within a formula

Rodrigo Pombo

New Member
Hi: how could I find if a specific number is contained within a formula. e..g A1: =Sum(1+2+0+5) then I want to identify that the numbers 1 or 0 are part of the formula to then use some conditional formatting.

Many thanks in advance.
 
See attached cells E4:E8 and their conditional formatting.
The formula in J4:J8 are not needed, they're just there to show how the CF formula works.
 

Attachments

  • Chandoo42141.xlsx
    9.3 KB · Views: 8
Thank you very much p45cal. One question, as soon as I enable editing it doesn't work. Is this a function you created?: _xlfn.FORMULATEXT. Again I'm very grateful with your response.
 
Hi !​
That means your Excel version is 'too old', the function does not exist …​
 
You both are right and I'm perplexed to find-out we are using 2010 in this corporation !!!. Once again thank you so much for helping me.
 
2010 version is good enough for usual needs, like my old 2003 version I keep on a tests computer !​
 
61414

If you don't have FORMULATEXT function, try this non-VBA way with helper column

1] Assume your formula cells put in E4:E8

2] Copy E4:E8 and paste to helper F4:F8

3] select F4:F8 >> Data >> Text to Columns >> Next >> Next >> in Step 3 of 3 click "Text" >> Finish
then, F4:F8 will show the cell formula

4] Select E4:E8 >> Conditional Formatting >> new rule >>
>> rule formula enter : =(LEN($F4)-LEN(SUBSTITUTE(SUBSTITUTE($F4,0,""),1,"")))>0
>> Format, select cell background color >> OK

Edit : hide the helper column in meet with your necessary

Regards
Bosco
 

Attachments

  • Conditional Formatting Cell with Specified Number.xlsx
    10.8 KB · Views: 5
Last edited:
Back
Top