+ Reply to Thread
Results 1 to 8 of 8

How to write a function to test if a cell contains a specific variable?

  1. #1
    Forum Contributor
    Join Date
    09-10-2016
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    487

    How to write a function to test if a cell contains a specific variable?

    The cell can contains any characters, for example, how to write a function to test if there is a specific variable in the cell?

    Given:
    1) Variable can be only letter.
    2) The cell string is in UPPER case status, so no need to consider LOWER case letter.

    Examples, test if below equation contains variable ABC:
    (ABCD + BHY)*2 > BUJ : It should return FALSE, since ABCD is not same as ABC.
    (AABC + BHY)*2 > BUJ : It should return FALSE, since AABC is not same as ABC.
    (KABCD + BHY)*2 > BUJ : It should return FALSE, since KABCD is not same as ABC.
    (ABCD + BHY)*2 > ABC : It should return TRUE.
    ABC + BHY*2 > BUJ : It should return TRUE.

    Thanks.
    Last edited by VAer; 05-25-2017 at 05:16 PM.

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,450

    Re: How to write a function to test if a cell contains a specific variable?

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Forum Contributor
    Join Date
    09-10-2016
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    487

    Re: How to write a function to test if a cell contains a specific variable?

    Quote Originally Posted by mehmetcik View Post
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I have listed 5 sample cases, your formula will return TRUE for all the them, while three of them should be FALSE.

    It is not that simple to use a formula.

  4. #4
    Forum Contributor
    Join Date
    09-10-2016
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    487

    Re: How to write a function to test if a cell contains a specific variable?

    Function CheckStr(StrLookIn As String, StrLookFor As String) As Boolean

    I would like to have a function like this, in this case StrLookIn is the equation string, StrLookFor is ABC

    Thanks.
    Last edited by VAer; 05-25-2017 at 07:09 PM.

  5. #5
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,450

    Re: How to write a function to test if a cell contains a specific variable?

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    09-10-2016
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    487

    Re: How to write a function to test if a cell contains a specific variable?

    [QUOTE=mehmetcik;4[/QUOTE]

    It is still wrong. NOT that simple to use a excel function. This formula is even worse, it returns FALSE for ABC+ BHY*2 > BUJ

    It is more complicated than what you think.

    Thanks anyway.

  7. #7
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,450

    Re: How to write a function to test if a cell contains a specific variable?

    You are using a strange character instead of a space.

    I over typed your spaces with a space and it worked.

  8. #8
    Forum Contributor
    Join Date
    09-10-2016
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    487

    Re: How to write a function to test if a cell contains a specific variable?

    It is just not that simple, I think it requires a few steps:

    1) Use Instr and check if it contains ABC. If yes and if there is a character before or after the ABC, we need to check the character, if the character is one of capital letter, then this is not the variable I am looking for. If no capital letter before/after that ABC or the character is not capital letter, then return TRUE, then end. If it is FALSE for first ABC, then move to below step.
    2) If above string is not the variable I am looking for, I need to identify that variable containing ABC, and replace that variable with blank, for example, I need to change (AABC + BGH)*2 > KKP to ( + BGH)*2 > KKP, and I still need to use Instr to check if ( + BGH)*2 > KKP contains variable ABC, and so on, until Instr returns 0. But as long as I found variable ABC, I can return TRUE and exit the loop without reaching Instr=0.
    Last edited by VAer; 05-25-2017 at 07:11 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How to inlcude another variable into the logical test of the IF function
    By kiddles13 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-18-2016, 02:30 PM
  2. Function to write Data to DB from specific rows and colomuns Plus specific Timestamp
    By Mudhafar.M in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-18-2013, 08:01 PM
  3. Replies: 2
    Last Post: 07-15-2010, 03:05 PM
  4. Write variable to cell in excel
    By iisjman07 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-27-2010, 07:22 PM
  5. [SOLVED] how to write function to find max deviation from specific number .
    By barbdee in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-15-2005, 11:15 PM
  6. Replies: 0
    Last Post: 03-24-2005, 08:06 PM

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