+ Reply to Thread
Results 1 to 11 of 11

Syntax for an "IF AND ISNUMBER SEARCH" function

  1. #1
    Registered User
    Join Date
    10-25-2017
    Location
    Texas, United States
    MS-Off Ver
    Excel 2016
    Posts
    18

    Syntax for an "IF AND ISNUMBER SEARCH" function

    Hello All! I am trying to write a formula into a cell to return a certain "text" string if another cell meets a condition AND another different cell contains a partial text string i.e. "*text*"

    What would this syntax look like in the below scenario:

    Cell A1 = Formula Cell where returned "text" string will appear (combination of B2 & C3 results)
    Cell B2 contains a conditional value that will be part of the returned "text" string in A1
    Cell C3 is the cell where I am searching for partial text string using "*text*"

    Thanks for the help!

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,000

    Re: Syntax for an "IF AND ISNUMBER SEARCH" function

    Attach a sample workbook (not image).

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Syntax for an "IF AND ISNUMBER SEARCH" function

    Hi,

    I think you are looking for something like
    =IF(AND(B2="something",ISNUMBER(SEARCH("text",C3))),B2&C3,"")
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  4. #4
    Registered User
    Join Date
    10-25-2017
    Location
    Texas, United States
    MS-Off Ver
    Excel 2016
    Posts
    18

    Re: Syntax for an "IF AND ISNUMBER SEARCH" function

    Sample file and desired logic attached
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-25-2017
    Location
    Texas, United States
    MS-Off Ver
    Excel 2016
    Posts
    18

    Re: Syntax for an "IF AND ISNUMBER SEARCH" function

    That syntax worked wonderfully! Next question is let's say I have a description that is a text string like "High Profile Block"...Can I code the return cell formula so that it can differentiate between the search for "*High Profile Block*" and "*Block*"?

  6. #6
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Syntax for an "IF AND ISNUMBER SEARCH" function

    Only by searching for the longer text first.

  7. #7
    Registered User
    Join Date
    10-25-2017
    Location
    Texas, United States
    MS-Off Ver
    Excel 2016
    Posts
    18

    Re: Syntax for an "IF AND ISNUMBER SEARCH" function

    I will try it and report back!

  8. #8
    Registered User
    Join Date
    10-25-2017
    Location
    Texas, United States
    MS-Off Ver
    Excel 2016
    Posts
    18

    Re: Syntax for an "IF AND ISNUMBER SEARCH" function

    Yessir! If I wanted to add another AND criteria (2 total) with the SEARCH?

  9. #9
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Syntax for an "IF AND ISNUMBER SEARCH" function

    Just add it to the existing ones separated by a comma- the syntax is
    AND(condition1,condition2,condition3)

  10. #10
    Registered User
    Join Date
    10-25-2017
    Location
    Texas, United States
    MS-Off Ver
    Excel 2016
    Posts
    18

    Re: Syntax for an "IF AND ISNUMBER SEARCH" function

    Perfect. Thank you for the help!

  11. #11
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Syntax for an "IF AND ISNUMBER SEARCH" function

    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)

Similar Threads

  1. [SOLVED] Formula: =IF((ISNUMBER(SEARCH("172.29.90.",$A3)))*(ISNUMBER(SEARCH("00-00-85",$F3))),"MF
    By dude6571 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-04-2017, 07:36 AM
  2. [SOLVED] VBA code - IF(ISNUMBER(SEARCH("Adobe", $A4)),"Adobe Pro")
    By dude6571 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-21-2017, 08:25 AM
  3. 7 nested IF statements with a SEARCH added IF(ISNUMBER(SEARCH({"INSUR","AP REFUND"}
    By michaelproctor001 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 12-10-2013, 02:13 PM
  4. Replies: 6
    Last Post: 02-25-2013, 03:16 AM
  5. Required "IF(ISNUMBER(SEARCH" related formula
    By nur2544 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-21-2013, 11:39 AM
  6. Alternative to nesting formula? "IF(ISNUMBER(SEARCH"
    By ad9051 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 03-19-2012, 06:12 AM
  7. using "isnumber(search" for a range of numbers
    By redhead522 in forum Excel General
    Replies: 5
    Last Post: 07-23-2009, 12:14 PM

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