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

Find a particular number into Formula

Greetings!!

I am searching a number say "5245" through Ctrl+F, but that column contains only formulas. Though apparently I could see "5245" into that column but I am unable to find that through Ctrl+F

Please suggest


Regards
Neeraj Kumar Agarwal
 
Hi Neeraj,

On the find and replace dialog box, try changing the LookIn option to Values..default value is set to Formulas..hence it is not finding the same..

Click on Options >> button to expand this dialog box


upload_2017-11-22_12-36-18.png

Hope this helps
 
Thanks for prompt reply!!

Suppose If I am having a formula in a cell and into few of the those cells I have manually added data i.e "+500" and I have to identify cells like this.

PS: "+500" is not common in all cells. Number may vary

Any suggestion
 
Last edited:
@Marc L: Actually I am looking for following

Suppose If I am having a formula in a cell and into few of the those cells I have manually added data i.e "+500" and I have to identify cells like this.

PS: "+500" is not common in all cells. Number may vary

Any suggestion
 
Hi ,

The usage CTRL + F followed by +* will find all instances of the + sign even within the formula , and not necessarily only those instances of the + sign followed by a constant.

Thus , we can have a formula :

=B5 + $B$10

as well as a formula :

=B5 + $B$10 + 500

The above CTRL F usage will find both instances.

If the constant value is variable , then there is no way that CTRL F will find only those instances.

That is why , one best practice is to comment this addition within the formula itself.

Thus , when we have the formula :

=B5 + $B$10 + 500

we can as easily make it :

=B5 + $B$10 + 500 + N("500")

Now , when we wish to find all such instances , all we have to do is use the text N(".

Narayan
 
You can try pattern matching... if "+500" isn't constant but number of digit is constant...

"+???" will exclude cell reference (if it's not 3 character in length).

upload_2018-2-8_11-44-59.png

Alternately if you have Excel 2013 or later...

=ISNUMBER(RIGHT(FORMULATEXT(B2),4)+0)

This will identify rows where constants are added at end in formula.

Filter data based on this column and do additional replace (or other operation) as needed.

But adding constants in formula especially when it isn't really constant. Is bad practice.

For ease of maintenance, use helper columns to store constants that need to be used in formula. And use reference to those cells.

It makes later maintenance much easier.
 
Back
Top