asked on # Excel 2010 formulas returning error because users accidentally entered a space in a cell.

I have prepared a census worksheet which contains formulas that refer to other cells. From time to time, users tell me something is wrong with the worksheet because "####" appears in the cell where the formula result should be. I have discovered that somehow they have entered a space in the cell that the formula refers to. Once I delete the space, the formula returns the correct answer.

Is there a way to write a formula so that it it treats a cell that contains a space in it as if it does not exist?

I have attached an example.. in Cell T13 of the "DEC" worksheet, I entered a space.

I would like to prevent users from entering spaces in columns Q thru Y, or else adjust formulas so that spaces are not recognized.

Monthly-2014.xls

Is there a way to write a formula so that it it treats a cell that contains a space in it as if it does not exist?

I have attached an example.. in Cell T13 of the "DEC" worksheet, I entered a space.

I would like to prevent users from entering spaces in columns Q thru Y, or else adjust formulas so that spaces are not recognized.

Monthly-2014.xls

Microsoft Excel

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

I will use the solution! Thanks for the quick response.