# Use a formula to check if another formula contains specific text/string

1. ## Use a formula to check if another formula contains specific text/string

Hello When trying to figure out that, got same thread here that was solved in the particular case, but not in mine.
The exact problem is that I need to know is which columns are in the formula and to may show the measurements.
Or
A1=B1*C1
A2=D1*C1

I need to know is there in the formula (of A1) "B" (that would means "kg") or D (that would be "pcs").

And in other words how to find text in formulas instead of values - is it possible at all?  Register To Reply

2. ## Re: Use a formula to check if another formula contains specific text/string

You can always find a Text in a string using FIND / SEARCH, and if you put an ISNUMBER in front of them, TRUE means found (they returns number), FALSE means not found (they return errors).
For instance, if you want to check whether "kg" is in A1, try ``Please Login or Register  to view this content.``  Register To Reply

3. ## Re: Use a formula to check if another formula contains specific text/string

Thanks Lem,
I want to find a particular text find within a formula... not in the value, that is my problem.
if the formula in A1 is B1*C1, I want to know is there the letter "B" - in the body of the formula...  Register To Reply

4. ## Re: Use a formula to check if another formula contains specific text/string

If so, why don't you just merge it inside the formula you want to search, for e.g instead of returning B1*C1, make it returns B1*C1&"kg"?  Register To Reply

5. ## Re: Use a formula to check if another formula contains specific text/string

I have a column which result is a price. I do not know which columns are in the formula of the price, thus do not know what measure to add in the description. I could manually see every row in the result column and to get "kg." or "pcs". but the are more than 5000 products.
A1=B1*C1
A2=D1*C1

I need to know is there in the formula (of A1) "B" (that would means "kg") or D (that would be "pcs").

A1 is the price. If it is obtained by multiply of B1 and C1 = "kilograms" and if it obtained by D1 and C1 = "pieces".

Sort of reverse engineering But seems impossible since the text functions always look in the value of the cells, not in the formula...  Register To Reply

6. ## Re: Use a formula to check if another formula contains specific text/string

You would need either a VBA UDF or you could use an XL4 macro function.

The VBA UDF (User Defined Function) would be more versatile and easier to use.

Do you know anything about VBA?  Register To Reply

7. ## Re: Use a formula to check if another formula contains specific text/string

Thanks Tony, that was my suspicion. About VBA UDF - I know exactly "something", for sure not enough for that job...  Register To Reply

8. ## Re: Use a formula to check if another formula contains specific text/string

Ok, that's about as much as I know so we have something in common! Copy and paste the code below into a general module: ``Please Login or Register  to view this content.``
Then, you can use it on the worksheet like this:

=GetFormula(A1)

If cell A1 contains a formula the function will return that formula as a text string.

If the cell does not contain a formula then the function returns a blank.  Register To Reply

9. ## Re: Use a formula to check if another formula contains specific text/string

Tony Valko
Valued Forum Contributor - now I see why it is here.

Thank you!  Register To Reply

10. ## Re: Use a formula to check if another formula contains specific text/string

You're welcome. Thanks for the feedback!   Register To Reply

11. ## Re: Use a formula to check if another formula contains specific text/string Seems that I've overestimated himself...
Thought it should be added in a macro which is obviously not true or I just do it wrong... ``Please Login or Register  to view this content.``
What I should do with the function?  Register To Reply

12. ## Re: Use a formula to check if another formula contains specific text/string

Copy the code below: ``Please Login or Register  to view this content.``

Right click any sheet tab

Select: View Code

Open the Project Explorer (if it's not already open): CTRL R

Select: your file name. It'll look something like this: VBAProject(your file name here)

Select: Insert>Module

Paste the code into the right side of the window that opens  Register To Reply

13. ## Re: Use a formula to check if another formula contains specific text/string

[shame on me emoticon here]

I do something wrong again...

4-24-2013 9-24-12 PM.jpg  Register To Reply

14. ## Re: Use a formula to check if another formula contains specific text/string

It looks like you have the function stored in another file. Why don't you just add the function to the file you want to use it in?  Register To Reply

15. ## Re: Use a formula to check if another formula contains specific text/string

You should be right again, I did a new file and added it, and works like a charm, dunno what happens with the problematic file but will investigate.

Thank you very much, Tony!  Register To Reply

16. ## Re: Use a formula to check if another formula contains specific text/string

You're welcome!   Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 