how can I set validation so no spaces are allowed ?

del

unread,

Apr 28, 2001, 7:00:27 AM4/28/01

to

I want to insure that no spaces are contained within a cell. I have
looked in data - validation and there doesn't seem to be the facility
to do this.

can anyone help ?
Thanks - Del

Dave Peterson

unread,

Apr 28, 2001, 8:32:36 AM4/28/01

to

one way is with data|Validation|custom

with a formula like this:

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

or

=ISERROR(FIND(" ",A1))

I bet you'll get more answers, too.

--

Dave Peterson
ec3...@msn.com

del

unread,

Apr 28, 2001, 7:33:02 PM4/28/01

to

but how do it to a range i.e.

don't allow blanks in any cell in the range a1:c20 ?

On Sat, 28 Apr 2001 07:32:36 -0500, Dave Peterson <ec3...@msn.com>
wrote:

Thanks - Del

Tom Ogilvy

unread,

Apr 28, 2001, 7:51:33 PM4/28/01

to

Select A1:C20
A1 is the activecell in the selection
Data=>Validation
Choose custom
=LEN(A1)=LEN(SUBSTITUTE(A1," ",""))

Since you are using relative cell references, the validation formula will
adjust to address each of the cells in the selection.

Regards,
Tom Ogilvy

del <sea...@XXXnew-ark.co.uk> wrote in message
news:3aeb5277.3109898@news.btinternet.com...

Jimmy L. Day

unread,

Apr 28, 2001, 7:55:25 PM4/28/01

to

That is really close, Dave. It works if you change the Format>Conditional
Formatting to Formula Is to:=LEN(A1)=LEN(SUBSTITUTE(A1,""," "))

David McRitchie

unread,

Apr 28, 2001, 10:34:19 PM4/28/01

to

Hi Jimmy,
Dave Peterson's formulas work just they way they were
presented. The first one
=LEN(A1)=LEN(SUBSTITUTE(A1," ",""))

compares the original length of A1 to the possibly
shorter length of A1 without spaces.
The object of the comparison is to return
TRUE OR FALSE. if both lengths are equal returns TRUE
so the validation allows entry; otherwise, validation prevents entry.

From HELP
SUBSTITUTE(text,old_text,new_text,instance_num)

HTH, (see Tom's reply for
David McRitchie, Microsoft MVP - Excel (site changed 2000-04-15)
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Search Page: http://www.geocities.com/davemcritchie/excel/search.htm
Google newsgroup search now covers Mar 29, 1995 to current

"Jimmy L. Day" <two...@cableone.net> wrote in message
news:3AEB586D...@cableone.net...

Murthy

unread,

Apr 28, 2001, 10:04:21 PM4/28/01

to

Jimmy:

I have tried yours, Dave's and also Tom's formulas but the spaces in the
selected range do not seem to go. (to avoid any typing mistakes, in fact I
copied your text and pasted it into the formula box of data validation with
Ctrl+V)

Will you please verify it once again?

Regards,
Murthy
Bangalore, in India

"Jimmy L. Day" <two...@cableone.net> wrote in message
news:3AEB586D...@cableone.net...
>

Tom Ogilvy

unread,

Apr 28, 2001, 11:24:13 PM4/28/01

to

Just to reinforce:
As David McRitchie said, David Peterson's formula that I posted in
conjunction with Data=>Validation worked fine for me. I entered it as I
described.

Tested in Excel 97 SR2

Regards,
Tom Ogilvy

J.E. McGimpsey

unread,

Apr 28, 2001, 11:28:40 PM4/28/01

to

Data Validation only applies to NEW entries- it won't make cells with
spaces already "seem to go".

For that you need a search and replace.

In article <u1Ls#iF0AHA.1540@tkmsftngp03>,
"Murthy" <murt...@hotmail.com> wrote:

--
J.E. McGimpsey
Remove NOSPAM from address to send email.

David McRitchie

unread,

Apr 28, 2001, 11:48:10 PM4/28/01

to

That's correct. Data validation can be created after
the cells have had values placed. You can "fix" those
already placed by Selecting the range and then using
Replace (ctrl+h)
in box1: a single space
in box2: (nothing)
Replace all

Data validation only applies to keyed in entry, you can paste
something in, you can do a global
change and undo all your good data validated entries
with Replace i.e. Replace "abc" with "ab cd"

HTH,

David McRitchie, Microsoft MVP - Excel (site changed 2000-04-15)
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Search Page: http://www.geocities.com/davemcritchie/excel/search.htm
Google newsgroup search now covers Mar 29, 1995 to current

"J.E. McGimpsey" <ne...@NmOcSgPiAmMpsey.com> wrote in message
news:news-10954B.2...@msnews.microsoft.com...

Murthy

unread,

Apr 28, 2001, 11:24:48 PM4/28/01

to

Sorry, I misunderstood the original question and messed around with few
extra posts!

I apologise to everyone!

Regards,
Murthy
Bangalore, in India

murt...@hotmail.com

"Tom Ogilvy" <twog...@email.msn.com> wrote in message
news:#KvGIrF0AHA.572@tkmsftngp05...

David McRitchie

unread,

Apr 29, 2001, 11:25:00 AM4/29/01

to

Hi Murthy,
I started with Dave Peterson's posting and I certain got additional
input in trying to answer your additional comments. Result:

Validate / Validation
http://www.geocities.com/davemcritchie/excel/validation.htm

HTH,

David McRitchie, Microsoft MVP - Excel (site changed 2000-04-15)
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Search Page: http://www.geocities.com/davemcritchie/excel/search.htm
Google newsgroup search now covers Mar 29, 1995 to current

"Murthy" <murt...@hotmail.com> wrote in message
news:exo$TxK0AHA.2236@tkmsftngp02...

Jimmy L. Day

unread,

Apr 29, 2001, 11:40:11 AM4/29/01

to

My apologies, David. I suffered a cranial-rectal inversion again; I hope it's
better now, but you can never be sure <sheepish g>. In the discussion, I was
seeing "conditional formatting", not "data validation" as the problem being
addressed.

Myrna Larson

unread,

Apr 29, 2001, 12:36:59 PM4/29/01

to

The formula is correct, but it will not remove spaces from the input. If the
input contains spaces, it just gives the user a message that the input is not
valid. The user must correct the problem himself.

Dave Peterson

unread,

Apr 29, 2001, 7:04:26 PM4/29/01

to

Another nice page!

But I think that there's a small problem with the no trailing/leading spaces.

you have:

=LEN(A1)=LEN(TRIM(A1))

But if you enter "asdfasdf asdfasdf", you'll see the problem. The worksheet
function TRIM gets rid of multiple internal spaces, too.

So I think your description should be more like:

Disallow spaces (leading/trailing/multiple internal)

If you just want leading/trailing disallowed:

=AND(LEFT(A1,1)<>" ",RIGHT(A1,1)<>" ")

(Hey, my name is in your site twice now! Wahoo!)

--

Dave Peterson
ec3...@msn.com

Murthy

unread,

Apr 29, 2001, 10:29:55 PM4/29/01

to

David,

That's nice. Atleast the confusion led to something creative. I will visit
your new page right away.

--

Regards,
Murthy
Bangalore, in India
murt...@hotmail.com

--

Regards,
Murthy
Bangalore, in India
murt...@hotmail.com

"David McRitchie" <DMcRi...@msn.com> wrote in message
news:udXU1CM0AHA.1416@tkmsftngp03...

David McRitchie

unread,

Apr 30, 2001, 12:08:18 AM4/30/01

to

Thanks Dave, page will be updated/corrected within the hour.
feedback on web pages are always especially appreciated.

David McRitchie, Microsoft MVP - Excel (site changed 2000-04-15)
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Search Page: http://www.geocities.com/davemcritchie/excel/search.htm
Google newsgroup search now covers Mar 29, 1995 to current

"Dave Peterson" <ec3...@msn.com> wrote in message
news:3AEC9DFA...@msn.com...
> Another nice page! [validation.htm]

del

unread,

Apr 30, 2001, 3:12:07 AM4/30/01

to

OK but how do I do this for a range ???

On Sun, 29 Apr 2001 08:54:48 +0530, "Murthy" <murt...@hotmail.com>
wrote:

Thanks - Del

Tom Ogilvy

unread,

Apr 30, 2001, 8:35:50 AM4/30/01

to

Just select the range and do the validation as if you were doing it for the
ActiveCell. Use relative addresses.

Regards,
Tom Ogilvy

"del" <sea...@XXXnew-ark.co.uk> wrote in message

news:3aec68a1...@news.btinternet.com...