+ Reply to Thread
Results 1 to 14 of 14

Using the IF function, to make entering a word, space then word correct

  1. #1
    Registered User
    Join Date
    05-21-2008
    Posts
    45

    Red face Using the IF function, to make entering a word, space then word correct

    How can I use the IF function, to make entering a word, space then word display correct and incorrect if not.

    Many thanks

  2. #2
    Registered User
    Join Date
    08-08-2007
    Location
    Vancouver
    MS-Off Ver
    Excel 2002, Excel 2007, Excel for Mac 2011
    Posts
    38

    Re: Using the IF function, to make entering a word, space then word correct

    Try inserting a AND function in your if statement...

    =if(AND(condition1=word,condition2=blank,condition3=word),good job, bad boy)

    Y

  3. #3
    Registered User
    Join Date
    05-21-2008
    Posts
    45

    Re: Using the IF function, to make entering a word, space then word correct

    I mean in reference to another cell

  4. #4
    Forum Contributor mubashir aziz's Avatar
    Join Date
    03-18-2009
    Location
    Lahore, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    533

    Re: Using the IF function, to make entering a word, space then word correct

    Try to be more specific what you need ????

    I have guess that you want as below :
    Lets you have words in Column A and you can put this formula in column B1 and then drag it down .....

    A1=HI
    A2=BYE
    A3=CALL
    A4=TELL

    now write below formula in B1
    B1=IF(A1="BYE"," Correct"," Incorrect")

    Now in your Cell B1 you will find " Correct"
    B2= " Incorrect"

  5. #5
    Registered User
    Join Date
    05-21-2008
    Posts
    45

    Re: Using the IF function, to make entering a word, space then word correct

    A person must type two words (identifiable by a space in between) in cell B3, if not cell D3 displays incorrect if they do write two words cell D3 displays correct.

  6. #6
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Using the IF function, to make entering a word, space then word correct

    try this.. this will give you true or false if there is no letter (or number) after space or space is in first place or no space at all

    =ISNUMBER(FIND(" ";I12;2)+1)

  7. #7
    Forum Contributor
    Join Date
    01-13-2009
    Location
    Mumbai
    MS-Off Ver
    Excel 2003, 2007
    Posts
    168

    Re: Using the IF function, to make entering a word, space then word correct

    Hi NatNat,
    Probably you could use the following formula in D3

    Please Login or Register  to view this content.
    You can apply =trim(CellReference) and remove all the extra spaces.

  8. #8
    Registered User
    Join Date
    05-21-2008
    Posts
    45

    Re: Using the IF function, to make entering a word, space then word correct

    Ok I will give it a go
    Last edited by NatNat; 05-06-2009 at 05:11 AM. Reason: typo

  9. #9
    Forum Contributor
    Join Date
    01-13-2009
    Location
    Mumbai
    MS-Off Ver
    Excel 2003, 2007
    Posts
    168

    Re: Using the IF function, to make entering a word, space then word correct

    Sorry NatNat,

    Following formula will surely solve your problem

    Please Login or Register  to view this content.
    Just try it once.. and let me know how it works..

    Regards,
    Vaibhav

  10. #10
    Forum Contributor mubashir aziz's Avatar
    Join Date
    03-18-2009
    Location
    Lahore, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    533

    Re: Using the IF function, to make entering a word, space then word correct

    some minor change in Zbor formula as it will not work if we have a space before / after and not between ..... So trim will make sure that there is a space between 2 words ......

    =ISNUMBER(FIND(" ",TRIM(A7),1)+1)

  11. #11
    Registered User
    Join Date
    11-21-2008
    Location
    India
    Posts
    6

    Re: Using the IF function, to make entering a word, space then word correct

    Yeah Vaibhav's solution answers the problem.

    There is another way of doing it.

    =IF(ISERROR(IF(FIND(" ",B3)<1,"Incorrect","Correct")),"Incorrect","Correct")

  12. #12
    Forum Contributor
    Join Date
    01-13-2009
    Location
    Mumbai
    MS-Off Ver
    Excel 2003, 2007
    Posts
    168

    Re: Using the IF function, to make entering a word, space then word correct

    Hi Poojabans,

    The formula that you have provided doesn't take care of many of the possibilities.. Though I dont know if they have to be considered..

    See the attached file..
    Attached Files Attached Files

  13. #13
    Forum Contributor mubashir aziz's Avatar
    Join Date
    03-18-2009
    Location
    Lahore, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    533

    Re: Using the IF function, to make entering a word, space then word correct

    I think It depends on user, what he want as if you have one trailing space in the end of name then both formulas will say its true .......If we have first, middle & last name but one of the formula is saying its wrong .... ........

    In database we can easily restrict user not to make mistakes but in excel its really hard .....

  14. #14
    Forum Contributor
    Join Date
    01-13-2009
    Location
    Mumbai
    MS-Off Ver
    Excel 2003, 2007
    Posts
    168

    Re: Using the IF function, to make entering a word, space then word correct

    Hi mubashir,
    thanks for pointing that out..

    Hope this formula works in all cases..

    Please Login or Register  to view this content.
    Yes mubashir, you are correct that it is difficult to restrict a user in excel.. as in the coding required is much more than that required in a database..

    but then excel has its own advantages..

    Regards,
    Vaibhav

+ 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