+ Reply to Thread
Results 1 to 10 of 10

If text does not contain (wildcard), return value of cell

  1. #1
    Registered User
    Join Date
    10-17-2014
    Location
    phoenix
    MS-Off Ver
    2010
    Posts
    5

    If text does not contain (wildcard), return value of cell

    Hello All,

    Long time lurker (thanks for all the problems you've helped solve, though you didn't know it), first time poster. I've encountered an issue that I've spent almost a good 6 hours researching, to no avail. Please see if you can help.
    I'm working in XL 2010.

    I have a HUGE data file that has a list of estimators (names) and about a million lines of data corresponding to each person. Ultimately, I need it cleaned up to move it into a dashboard that is usable by management. For this, I had planned to use dynamic pivot tables to avoid having to manually update every line of data...HOWEVER, the original author of this enormous data file left lines available (mostly titled "Estimator #1" "Estimator#2" "Available," etc) to account for potential additions to his already mostly-automated spreadsheet. I didn't think this would pose such a substantial issue but I guess it has. I'm not sure the most efficient formula and, at this point, I can't even get anything to work.

    What I'm trying to tell it to do is, "If the cell <>, "Estimator*"), return the value of that cell.

    I have tried using:

    IF statement -- has issues with returning wildcard text in a logical function, I guess.
    IF(SEARCH -- Returning all cell values, regardless of = or <>.
    ISTEXT -- returns only FALSE.
    IF(ISTEXT -- Returns no values with wildcard in text string.
    IF(ISTEXT(SEARCH -- Returns all cell values, regardless of = or <>.

    I have tried all of these combinations with either a wildcard or, if that didn't return values, a <>.
    And, while admittedly newer to vlookups, I couldn't find a workable way to do this.

    All I can find that is working (I digressed just to make sure I wasn't writing formulas incorrectly) is the COUNTIF...this isn't an option, as I need the actual names and not a 1, 0.

    sample.xlsx

    I am at a total loss and thankful for anyone who puts forth the effort to teach me to solve this problem.

    Thank you!

  2. #2
    Valued Forum Contributor ranman256's Avatar
    Join Date
    07-29-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2003
    Posts
    1,051

    Re: If text does not contain (wildcard), return value of cell

    I might have something but I dont know WHERE to return the value. In your example: A6 has Estimator, so you want Estimator#1, put where? Or the values of Estimator's in B,C, & D6 put where?

  3. #3
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: If text does not contain (wildcard), return value of cell

    try
    =IF(ISERROR(SEARCH("estimator*",A2)),A2,"")
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Forum Contributor
    Join Date
    10-16-2014
    Location
    Center Line, Michigan, United States
    MS-Off Ver
    MS Office 2013
    Posts
    139

    Re: If text does not contain (wildcard), return value of cell

    Hello sabusby,

    Have you tried a LEFT formula?

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Please click the star (add rep) if I helped!

  5. #5
    Registered User
    Join Date
    10-17-2014
    Location
    phoenix
    MS-Off Ver
    2010
    Posts
    5

    Re: If text does not contain (wildcard), return value of cell

    Wow! You guys are quick!

    Re: Ranman: I'm cleaning them to another sheet and do not want to see the cells which contain "Estimator" anything. They will muddy-up the pivot-tables I plan to develop from this data. I only need to see current employees and their sales data. "Estimator" is basically a placeholder with no meaningful information but could potentially hold some in the future.

    Re: MartinDWilson: Your formula worked! I am forever thankful! I know nothing of the ISERROR function (and little about the search function) but was just researching when I saw the replies to this thread!

    Re: Loganeb: I haven't had a use for the LEFT function but I will definitely research this as well. Everyone around here thinks I'm an excel whiz--I try to tell them I'm only doing basic functions but no one listens!

    Thank you all so much for your help. This is seriously invaluable after all the time I've spent staring at it and pulling my hair out!

  6. #6
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: If text does not contain (wildcard), return value of cell

    You can also use this

    =IF(COUNTIF(A2,"estimator*"),"",A2)
    Last edited by AlKey; 10-27-2014 at 02:21 PM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  7. #7
    Registered User
    Join Date
    10-17-2014
    Location
    phoenix
    MS-Off Ver
    2010
    Posts
    5

    Re: If text does not contain (wildcard), return value of cell

    Oh my gosh. I knew there was a reason this seemed like such an easy problem to solve. :\

    Is it possible to take this a step further and include an additional <>? Such as you don't want "estimator*" or "manager*" ?

    Wrapping my brain around nested functions and order of execution has been a challenge :\

  8. #8
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: If text does not contain (wildcard), return value of cell

    Sure

    =IF(OR(COUNTIF(A2,{"estimator*","manager*"})),"",A2)

    Corrected
    Last edited by AlKey; 10-27-2014 at 02:29 PM.

  9. #9
    Registered User
    Join Date
    10-17-2014
    Location
    phoenix
    MS-Off Ver
    2010
    Posts
    5

    Re: If text does not contain (wildcard), return value of cell

    You are all too fantastic! Heroes of the day!

    ETA: It wasn't working at first and thought I had just missed some syntax. Thank you so much!

  10. #10
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: If text does not contain (wildcard), return value of cell

    You're welcome. Don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

+ 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. If cell contains text1, text2 or text 3 return this text, otherwise return X
    By bukmanodrama in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-26-2013, 09:24 AM
  2. [SOLVED] how to use a wildcard with an IF formula to search for text in a cell
    By nje in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 10-23-2012, 07:40 AM
  3. VBA Wildcard character / find cell which CONTAINS a text
    By adgjqetuo in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-12-2009, 04:18 AM
  4. Compare string, wildcard, text/cell formatting
    By 0-0 Wai Wai ^-^ in forum Excel General
    Replies: 12
    Last Post: 09-07-2005, 02:05 AM
  5. Find wildcard text within a cell
    By indiana1138 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-31-2005, 08:05 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