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

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
0
Jugglertwo (10)
12/2/2005 4:33:05 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
361 Views

Similar Articles

[PageSpeed] 6

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


"Jugglertwo" wrote:

> 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
0
12/2/2005 4:49:01 PM
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

"Jugglertwo" wrote:

> 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
0
Bernard1 (17)
12/2/2005 4:50:02 PM
Reply:

Similar Artilces:

Entire Active Directory visible when Allowing Users to Edit Ranges
Excel XP on Windows 2000. TS Environment running Citrix. Following Problem: Tools - Protection - Allow user to edit ranges - new - permissions - add. This provides a list of the ENTIRE active directory of the domain. Where is the switch to turn this off or to limit the access? Am I in the right newsgroup even? Any hints will be greatly appreciated. ...

macro for coloring cells
is there a macro for coloring the cells up? i'd like to color up selected cells as; say by pressing "ctrl + p". hal9000 You would need to build a macro to do what yoiu whant. But why don' you use the available toolbar item that does that with one mouse clic - Available on the Formatting toolba -- Message posted from http://www.ExcelForum.com There are no macros by default. You make them yourself and get prompted "this workbook contrains macros" when you open those files afterwards. In this case, simply record a macro while coloring a cell and you're ...

field service allow setup of cycle charge allowed up to
Field service needs to allow the setup in meter readings to have an amount for each meter reading that is not charged versus just a maximum in the base field. For instance the customer each meter reading gets the first 1000 cycles at no charge and .05 per cycle over the allowance. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsre...

define a cell with the value of anothe cell
I'm very new to excel and i think i just don't understand a basic function but i couldn't find it under the help menu. how can you define a cell using a letter and then a value of another cell? here is an example W15=5 D5=10 my guess was =D(W15) which i would like to equal 10, but i get a name error Hi Todd Try =INDIRECT("D"&W15) -- Regards Roger Govier Todd Duncombe <Todd Duncombe@discussions.microsoft.com> wrote: > I'm very new to excel and i think i just don't understand a basic > function but i couldn't find it under the help me...

If two cells don't equal each other, notify; if they do equal each other, do a "sum"
I'm building a quoting tool for my sales organization and I want to make sure I'm using the most recent pricelist. My operations group sends out a spreadsheet with the most recent pricing that I save to a specific location. Then I have a worksheet in my quoter workbook that syncs with what operations sends when I do a "refresh all". There is a unique identifier in the pricelist that identifies it as the most recent version. What I want to do is compare the identifier in the worksheet called "Database" against the identifier in the pricelist and if they're no...

Automatically control the font of a cell when a function is used
I have a my custom function say myfun(arguments) I would like that whenever I use this function in any cell of a workbook (sheet) there should be always be the same font lets say Times New Roman, the font size to be 10, font color automatic. I tried to make another sub that will check every time the sub is entered and ensure that the cell is well with the desired font type. But, I could not get through the sub. Any help in this regard will be appreciated. Thanks in advance. I don't think that you can make changes to the worksheet from within the UDF but you could use a Workshe...

Does Outlook 2003 allow multiple Exchange accounts?
Hi, I was wondering if Microsoft figured out a way to have more than one Exchange accounts in Outlook 2003? I realize that I can have multiple Exchange accounts in Outlook 2002 provided that I set them up as POP3 or IMAP. I'm talking about having multiple pure Exchange accounts w/ calendar and everthing. Thanks, Sam Nope. Only 1 Exchange account is still supported when working against 2 or more sites. -- Neo [MVP Outlook] Due to the Swen virus, all e-mails sent to this account will be deleted w/out reading. "Sam" <sam@iqinternet.com> wrote in message news:e1sEVVao...

automatically suppress Space Before after a column break (Word 200 01-27-10
After a Continuous section break, I have created 2 columns. Styles have Space Before and/or After. When I force a column with a column break, the second column is keeping the Space Before. Please note I have already selected the compatibility option 'suppress Space Before after a hard page break or column break'. What am I missing? The fact that this is a continuous section seems to be the issue. What you can do is remove the spacing before manually (as direct formatting) from the paragraph after the column break. -- Stefan Blom Microsoft Word MVP "...

Changing color of cell
Excel: In an if statement can i change cell fill or background color if correct Noit in the cell formula but you can use Conditional Formatting to change the color of the cell. Format>Conditional Formatting:Formula is =A1="Noelsy" Format to a color. If A1 contains Noelsy the cell will be colored. If it contains Gord it will not be colored. Or for this simple example CF>Cell Value is equal to Noelsy would return the color if true. Gord Dibben MS Excel MVP On Fri, 30 Nov 2007 20:21:01 -0800, Noelsy <Noelsy@discussions.microsoft.com> wrote: >Excel: In an if...

read only form allows date to change
I have a form that I open in read only that has a calendar popup for the date fields. When I open the form the calendar pop up allows the date to be changed. The rest of the fields are read only. Any suggestions. Thanks Private Sub ViewActivityNote_Click() On Error GoTo Err_ViewActivityNote_Click Dim stDocName As String Dim stLinkCriteria As String stDocName = "FRM - Activity Note" stLinkCriteria = "[activitynoteid]=" & Me![activitynoteid] DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormReadOnly Exit_ViewActivityNote_Click: Exi...

Looping thru a range of cells
How do I loop through a range of cells (A1:Y40) looking for a match to another cell (Z1). There will be multiple matches. When a match is found, I want to subtract the 2 cells prior to the found cell from each other and accumulate all of the results. THANK YOU!! -John Smith If the value in Z1 is found in A1, what are "the 2 cells prior to the found cell from each other"? In article <01113F09-F1AC-4433-804E-C437936CE60F@microsoft.com>, COBOL Dinosaur <COBOLDinosaur@discussions.microsoft.com> wrote: > How do I loop through a range of cells (A1:Y40) looking for...

format of go to cells
using excel 2007, using find-go to special formulas, the shading on the formula cells is the lightest blue and very difficult to see. Can I change this fill color format? charlie Unfortunately I don't think you can. There have been a lot of complaints about shading of cells in XL2007. -- Regards Roger Govier "Charlie Woll" <cwoll@citlink.net> wrote in message news:eiB2opYcIHA.3572@TK2MSFTNGP02.phx.gbl... > using excel 2007, using find-go to special formulas, the shading on the > formula cells is the lightest blue and very difficult to see. Can I > cha...

Return cell based on another cell help!
Hi, I have a spreadsheet with a list of codes in column A, a Master list of codes in column C, and a Master description in D that is associated with the values in C. If a code exists in A, I would like to check if the same code exists in C. If so, I would like to populate column B with the description in D. Can anyone show me how to do this please? Try this in B1: =IF(ISNA(MATCH(A1,$C$1:$C$100,0)),"No Match",VLOOKUP(A1,$C$1:$D$100,2,0)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============...

Too many cell formats error
I am receiving a "too many cell formats" error, which is preventing me from doing almost anything with the spreadsheet we are working on. Deleteing current formatting is not an option. Is anyone aware of a way to increase this limit or other ways to solve this problem ???? ...

How do I stop a cell from incrementally adding up?
I have a 2 cells merged, the data in the cells are always 1 of three choices ( 2-1/2, 3-1/2, 4-1/2) If I select my choice of the three and try to extend it to the next cell it automatically raises it by 1 with each cell added. For example: cell A:1 = 2-1/2 when I drag that the next cell cell B:1 = 3-1/2 then cell C:1 = 4-1/2 then cell D:1 = 4-1/2 etc. I don't have this problem when cells are not merged, is there an alternate way of merging the cells so this does not occur? Thanks in advance. EggHeadCafe.com - .NET Developer Portal of Choice http://www.eggheadcafe.com Hold the ctrl key ...

Payroll should allow concurrent builds by different users
In a company that is heavy payroll, all checks are processed in the same day but by different users for different subsets of employees. Allowing concurrent builds would be nice. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolution...

NUL characters are not allowed
Hi, We have install the exchange 2003 and we are encountering intermitten problem when sending email to internet (only to a certain email domain other are alright). When that happens, we receive a "NUL characters ar not allowed" error. <mail.anonymous.com #5.5.0 smtp;550 Requested action not taken: NU characters are not allowed.> This error usually happens when we reply to a certain email domain. When this happens, we would retry 2 or 3 times, it usually i successfully after 2 or 3 tries. It only affects a small number of email domain. Other email domain ha no problem. H...

Deleting Cells, but not ones with formulars
Hello, I have a large sheet that people enter data into each week. I want t be able to hightlight a large area of the sheet and delete the data i those selected cells, but not the formulars that are in some of th cells. Anyone know how I can do this? Any help apreciated! -- scriblesvur ----------------------------------------------------------------------- scriblesvurt's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2439 View this thread: http://www.excelforum.com/showthread.php?threadid=38001 This worked for me: Sub DeleteCells() Set rng = Range("...

Formatting Lost When Multiple Cells are blank
I provide technical support for software users. These users export data from the software with multiple columns and multiple rows. One column for End Date is formatted as Custom format Type: m/d/yyy" "h\:mm\:ss AM/PM. These files are often very large, sometimes over 55,000 rows. The problem happens when there are multiple rows in a row without data. It seems as if the formatting is lost. For example, the first ten rows have data recorded (date and time) and there are then 500 rows with blank data and then the next ten rows (rows 511 through 521) have data. These cell...

Linking a cell to a list and then multiselect
I have a regualar excel sheet that I am trying to populate. I want to populate each cell from a list on one of the sheets in the excelbook. I have about 1000 cells in different sheets that I need to populate. How do I link each of the 1000 cells to that list and also be able to do a multiselect? --- Message posted from http://www.ExcelForum.com/ put in a reference to the cells =Sheet1!A1 what do you mean by Multiselect? -- Regards, Tom Ogilvy "Wango" <Wango.z9i6n@excelforum-nospam.com> wrote in message news:Wango.z9i6n@excelforum-nospam.com... > I have a regualar e...

Allowing pictures to download in Outlook 2002
My boss is using Outlook 2002 and I can't find this setting: Pictures don't download in his emails In 2003 it's under Tools - Options - Security - Change Automatic Download settings. Can anyone help? Thanks! ...

Format Cell contents as I.P. address
Is there a way to force the contents of a cell to conform to the format of an I.P. address (e.g. nnn.nnn.nnn.nnn) ? I looked in the tutorial format cell and found custom but no useful tutorial for how to do it. Thanks -- Lionel B. Dyck <>< AIM ID: lbdyck Yahoo IM: lbdyck Homepage http://www.lbdsoftware.com/ Blog: http://randommgmt.blogspot.com/ Custom ###"."###"."###"."### i.e. 123456789123 will be formatted as 123.456.789.123 Just as long as you realize that the number is still the original number you entered. Gord Dibben MS Excel MVP On ...

remove spaces between words. replace() not working
I am trying to remove spaces between words in the db and replace does not seem to be working. I used it in the query window. may be I am not entering it properly. my query: select replace(field, ' ',' ') from tableName does not execute without "select". Thanx for the help in advance. At least one reply to your earlier question today with the same request. -- Duane Hookom Microsoft Access MVP "mapsNoob" wrote: > I am trying to remove spaces between words in the db and replace does > not seem to be working. > I used it in the query window. ma...

Missing Cells
I created a workbook in Excel 2003, and entered various data. Some of the data was typed in, and then some whas copied from other workbooks. I then proceeded to have the columns auto filtered. Now when i use this feature, i am not able to auto filter to some cells? The only one that are of importance are the one containing Text (which are formated that way). Does anyone know why this is? ...

what does whitespace not allowed in this location mean and how do.
i am importing data in csv to excel but it says 'file not loaded- whitespace not allowed in this location' what does that mean? and how do i cahnge it? ...