+ Reply to Thread
Results 1 to 10 of 10

formula to return blank rather than a "0"

  1. #1
    Registered User
    Join Date
    09-27-2011
    Location
    N.Ireland
    MS-Off Ver
    Excel 2003
    Posts
    13

    formula to return blank rather than a "0"

    this is my formula

    =IF(ISNUMBER(SEARCH("Lilac",G21)),150)+IF(ISNUMBER(SEARCH("Red",G21)),-50)

    it returns a 0 , what do i need to add to it to return a blank cell instead ?

    thanks in advance

  2. #2
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    re: formula to return blank rather than a "0"

    Can both Lilac and Red be present?

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.

  3. #3
    Registered User
    Join Date
    09-27-2011
    Location
    N.Ireland
    MS-Off Ver
    Excel 2003
    Posts
    13

    re: formula to return blank rather than a "0"

    Quote Originally Posted by Domski View Post
    Can both Lilac and Red be present?

    Dom

    no, the user selects the cell contents from a pickbox, lilac and red never appear in the same cell at same time.

    its probably a simple formula to add to mine =IF(ISBLANK(G21),"",G21) or similar, but i dont know where to add it into my formula ?

    thanks

  4. #4
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    re: formula to return blank rather than a "0"

    So are Lilac and Red part of a longer text string? I'm just trying to ascertain the reason for using ISNUMBER/SEARCH and the best formula.

    Dom

  5. #5
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    re: formula to return blank rather than a "0"

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    MOST PEOPLE REQUIRE HELP SO WHY PUT IT IN THE THREAD TITLE? EDITED FOR YOU
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  6. #6
    Registered User
    Join Date
    09-27-2011
    Location
    N.Ireland
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: formula to return blank rather than a "0"

    Quote Originally Posted by Domski View Post
    So are Lilac and Red part of a longer text string? I'm just trying to ascertain the reason for using ISNUMBER/SEARCH and the best formula.

    Dom
    yes Dom, the words "red" and lilac" are parts of a longer string.

  7. #7
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: formula to return blank rather than a "0"

    Like this then:

    =IF(ISNUMBER(SEARCH("Lilac",G21)),150,IF(ISNUMBER(SEARCH("Red",G21)),-50,""))

    Dom

  8. #8
    Registered User
    Join Date
    09-27-2011
    Location
    N.Ireland
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: formula to return blank rather than a "0"

    Quote Originally Posted by Domski View Post
    Like this then:

    =IF(ISNUMBER(SEARCH("Lilac",G21)),150,IF(ISNUMBER(SEARCH("Red",G21)),-50,""))

    Dom

    sorry didnt work, when i used this formula, i got a "150" placed in the cell?

    to clarify the problem. the formula would go into say cell G22, when Cell G21 contains the text "Lilac" or "Red", it returns either a 150 if "Lilac" or "-50" if "Red" text is found. What happens though when it finds no text in cell G21 it shows a "0" in G22, i want it to give a blank cell instead.

    thanks
    Last edited by xlr8r; 09-30-2011 at 09:22 AM.

  9. #9
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: formula to return blank rather than a "0"

    Really? It checks for Lilac, puts 150 in if found, if not checks for Red and puts -50 in if found and failing both those it'll return "".

    Dom

  10. #10
    Registered User
    Join Date
    09-27-2011
    Location
    N.Ireland
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: formula to return blank rather than a "0"

    thanks Dom

    finally got it working.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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