Dataval question
 

I use the below formula in datavalidation 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)

Dataval question
 

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" wrote in message
...
I use the below formula in datavalidation 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)

Dataval question
 

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" wrote in message
...
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" wrote in message
...
I use the below formula in datavalidation 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)

Dataval question
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
I use the below formula in datavalidation 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)

Dataval question
 

Perfect. Thank you!

"Dana DeLouis" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
I use the below formula in datavalidation 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)