+ Reply to Thread
Results 1 to 16 of 16

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

  1. #1
    Registered User
    Join Date
    04-23-2013
    Location
    Bulgaria, Varna
    MS-Off Ver
    Excel 2003
    Posts
    8

    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?

    Thank you in advance!

  2. #2
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    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.
    (copy pasta from Ford)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools

    Regards,
    Lem

  3. #3
    Registered User
    Join Date
    04-23-2013
    Location
    Bulgaria, Varna
    MS-Off Ver
    Excel 2003
    Posts
    8

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

  4. #4
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    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"?

  5. #5
    Registered User
    Join Date
    04-23-2013
    Location
    Bulgaria, Varna
    MS-Off Ver
    Excel 2003
    Posts
    8

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

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  7. #7
    Registered User
    Join Date
    04-23-2013
    Location
    Bulgaria, Varna
    MS-Off Ver
    Excel 2003
    Posts
    8

    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...
    Last edited by musketeer; 04-24-2013 at 02:03 AM.

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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.

  9. #9
    Registered User
    Join Date
    04-23-2013
    Location
    Bulgaria, Varna
    MS-Off Ver
    Excel 2003
    Posts
    8

    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!

  10. #10
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

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

    You're welcome. Thanks for the feedback!

  11. #11
    Registered User
    Join Date
    04-23-2013
    Location
    Bulgaria, Varna
    MS-Off Ver
    Excel 2003
    Posts
    8

    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?

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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.
    With your file open...

    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)

    Right click your file name

    Select: Insert>Module

    Paste the code into the right side of the window that opens

    Close the window to return to Excel

  13. #13
    Registered User
    Join Date
    04-23-2013
    Location
    Bulgaria, Varna
    MS-Off Ver
    Excel 2003
    Posts
    8

    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
    Attached Images Attached Images

  14. #14
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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?

  15. #15
    Registered User
    Join Date
    04-23-2013
    Location
    Bulgaria, Varna
    MS-Off Ver
    Excel 2003
    Posts
    8

    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!

  16. #16
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

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

    You're welcome!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1