Don't allow a space in a cell...how to prevent?

[Guest]

One of my students asked an interesting question yesterday in Excel.
We were working on IF statements and if a cell had a student's name in it,
the IF statement proceeded to do the FALSE Result, if not, it did the TRUE
Result.
=if(A1="","student missed class","student attended class")
I explained that if a student hit the space bar and entered "a space", this
space would be considered something and the IF statement would produce the
FALSE Result of "student attended class".

Student's question (and mine): Is there a way to prevent someone from
entering "a space" in the cell? Even though it looks blank, the space will be
there causing the IF statement to produced "the wrong" result. Hope this
makes sense.

Any suggestions would be greatly appreciated!
Thanks!
Jugglertwo

 

[Guest]

You have more than one option here:

1)You can prevent a space from appearing in any part of the cell by using
Data Validation:
Example:
Select Cell A1
Data>Data Validation
Allow: Custom
Formula: =NOT(ISNUMBER(SEARCH(" ",A1)))

2)You can also use Data Validation to prevent a space being the only
contents of the cell, but the user could type more than one space, right?

3)Alter your forumula to differentiate between BLANK and "".

=if(ISBLANK(A1),"student missed class","student attended class")


Do any of those help?

***********
Regards,
Ron

 

[Guest]

There may be a fancier way of doing it but one option is to
a) Fine a spare area in the spreadsheet and type a list of values you would
be happy for users to enter(e.g. Student missed class, Student Attended
class).
b) Highlight the range of cells that the values would normally be typed into
c) Select Data, Validation, Allow - select List from the drop down box, then
in the Source box highlight the list you created in a) above.

Hope this helps
Bernard