Data>val question

[Steph]

I use the below formula in data>validation to not allow commas in the cell.
Is there a way to add to this to also not allow more than 1 blank space in
the cell? Thank you!!

=IF(ISERROR(FIND(",",B5)),1,0)

[Chip Pearson]

Use a validation formula like

=(LEN(B5)-LEN(SUBSTITUTE(B5," ","")))<2

Note that there is one space between the first set of quote
marks, and nothing between the second set of quote marks.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Steph" <verysmallrox@yahoo.com> wrote in message
news:%23ItUTMRRFHA.1096@tk2msftngp13.phx.gbl...
>I use the below formula in data>validation to not allow commas
>in the cell.
> Is there a way to add to this to also not allow more than 1
> blank space in
> the cell? Thank you!!
>
> =IF(ISERROR(FIND(",",B5)),1,0)
>
>

[Steph]

Hi Chip. Thanks for the formula. One folluw up question - Can we combine
the two formulas into one, so as to not allow commas OR more than one space
in the cell?

So essentially, combine:
=(LEN(B5)-LEN(SUBSTITUTE(B5," ","")))<2 and
=IF(ISERROR(FIND(",",B5)),1,0)

Thanks!!


"Chip Pearson" <chip@cpearson.com> wrote in message
news:uJ1ckbRRFHA.1172@TK2MSFTNGP12.phx.gbl...
> Use a validation formula like
>
> =(LEN(B5)-LEN(SUBSTITUTE(B5," ","")))<2
>
> Note that there is one space between the first set of quote
> marks, and nothing between the second set of quote marks.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
> "Steph" <verysmallrox@yahoo.com> wrote in message
> news:%23ItUTMRRFHA.1096@tk2msftngp13.phx.gbl...
> >I use the below formula in data>validation to not allow commas
> >in the cell.
> > Is there a way to add to this to also not allow more than 1
> > blank space in
> > the cell? Thank you!!
> >
> > =IF(ISERROR(FIND(",",B5)),1,0)
> >
> >
>
>

[Dana DeLouis]

I think this should work for you.
=AND((LEN(B5)-LEN(SUBSTITUTE(B5," ","")))<2,ISERROR(FIND(",",B5)))

Note that you do not need IF( ) with ISERROR because that function returns
True/False anyway.
HTH.
--
Dana DeLouis
Win XP & Office 2003


"Steph" <verysmallrox@yahoo.com> wrote in message
news:ed25YGbRFHA.904@tk2msftngp13.phx.gbl...
> Hi Chip. Thanks for the formula. One folluw up question - Can we combine
> the two formulas into one, so as to not allow commas OR more than one
> space
> in the cell?
>
> So essentially, combine:
> =(LEN(B5)-LEN(SUBSTITUTE(B5," ","")))<2 and
> =IF(ISERROR(FIND(",",B5)),1,0)
>
> Thanks!!
>
>
> "Chip Pearson" <chip@cpearson.com> wrote in message
> news:uJ1ckbRRFHA.1172@TK2MSFTNGP12.phx.gbl...
>> Use a validation formula like
>>
>> =(LEN(B5)-LEN(SUBSTITUTE(B5," ","")))<2
>>
>> Note that there is one space between the first set of quote
>> marks, and nothing between the second set of quote marks.
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>>
>> "Steph" <verysmallrox@yahoo.com> wrote in message
>> news:%23ItUTMRRFHA.1096@tk2msftngp13.phx.gbl...
>> >I use the below formula in data>validation to not allow commas
>> >in the cell.
>> > Is there a way to add to this to also not allow more than 1
>> > blank space in
>> > the cell? Thank you!!
>> >
>> > =IF(ISERROR(FIND(",",B5)),1,0)
>> >
>> >
>>
>>
>
>

[Steph]

Perfect. Thank you!

"Dana DeLouis" <delouis@bellsouth.net> wrote in message
news:%2346wmcbRFHA.252@TK2MSFTNGP12.phx.gbl...
> I think this should work for you.
> =AND((LEN(B5)-LEN(SUBSTITUTE(B5," ","")))<2,ISERROR(FIND(",",B5)))
>
> Note that you do not need IF( ) with ISERROR because that function returns
> True/False anyway.
> HTH.
> --
> Dana DeLouis
> Win XP & Office 2003
>
>
> "Steph" <verysmallrox@yahoo.com> wrote in message
> news:ed25YGbRFHA.904@tk2msftngp13.phx.gbl...
> > Hi Chip. Thanks for the formula. One folluw up question - Can we
combine
> > the two formulas into one, so as to not allow commas OR more than one
> > space
> > in the cell?
> >
> > So essentially, combine:
> > =(LEN(B5)-LEN(SUBSTITUTE(B5," ","")))<2 and
> > =IF(ISERROR(FIND(",",B5)),1,0)
> >
> > Thanks!!
> >
> >
> > "Chip Pearson" <chip@cpearson.com> wrote in message
> > news:uJ1ckbRRFHA.1172@TK2MSFTNGP12.phx.gbl...
> >> Use a validation formula like
> >>
> >> =(LEN(B5)-LEN(SUBSTITUTE(B5," ","")))<2
> >>
> >> Note that there is one space between the first set of quote
> >> marks, and nothing between the second set of quote marks.
> >>
> >>
> >> --
> >> Cordially,
> >> Chip Pearson
> >> Microsoft MVP - Excel
> >> Pearson Software Consulting, LLC
> >> www.cpearson.com
> >>
> >>
> >>
> >> "Steph" <verysmallrox@yahoo.com> wrote in message
> >> news:%23ItUTMRRFHA.1096@tk2msftngp13.phx.gbl...
> >> >I use the below formula in data>validation to not allow commas
> >> >in the cell.
> >> > Is there a way to add to this to also not allow more than 1
> >> > blank space in
> >> > the cell? Thank you!!
> >> >
> >> > =IF(ISERROR(FIND(",",B5)),1,0)
> >> >
> >> >
> >>
> >>
> >
> >
>
>