data validation - text or blank cell

[andrewm]

Hi - I am trying to do a data validation rule - I want the cells to have text or numbers or be completely empty (blank), if not an alert happens. I appreciate it is simple.

regards

Andrewm

[CK76]

But that means you accept all values. Text will be any text or text + numeric, number will include (float, int, date, time)...

Do you really need validation on that? Or is there more detail missing?

[andrewm]

I have a conditional formatting to change the cell's colour when the cell is blank. however when the cell is not blank the conditioning does not work. I want an alert if the cell is not blank. Someone has entered something (space bar etc) and assumes the cell is blank, but is not.

if there is text - this is ok.
if the cell looks blank, but is not - conditioning formating doesnt happen. Thus needs an alert

andrewm

[CK76]

That's bit difficult to deal with formula alone. As seemingly blank cell can hold any number of characters.

Ex: Non-printing characters, one or more spaces, carriage return etc.

[BMV]

The combination of CLEAN,SUBSTITUTE and TRIM can help you

Please Login or Register  to view this content.

TRUE will be in the case of cell A1 looks like empty.