Data Validation - No Spaces

[DavidWe]

I would like to not allow a user to enter spaces in a series of characters in a cell. I currently have the data validation set to text length = 7. There should be no spaces within those 7 characters. Is there a way to use the Data Validation function for this and still keep the text length limitation? I could probably write some code to have a message pop up but thought there might be another way. I don't want to just strip out the spaces or replace them with zeros because the sequence really needs to be seven characters and match another sequence of characters elsewhere. The user needs to be forced to enter seven characters without spaces. I will need to have this in 2003 and 2007. I think the solution will be the same for both versions.

Thanks for any help.

[barry houdini]

Try using Data Validation with this custom formula (assuming data in A1)

=(LEN(A1)=7)*(LEN(SUBSTITUTE(A1," ",""))=7)

[pgc01]

A similar one:

=(LEN(A1)=7)*(SUBSTITUTE(A1," ","")=A1)

[barry houdini]

A similar one:

=(LEN(A1)=7)*(SUBSTITUTE(A1," ","")=A1)

Ah, yes, that's better......

[DavidWe]

Thanks, PGC and Barry.

This works:
=(LEN(A1)=7)*(SUBSTITUTE(A1," ","")=A1)