Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Question on looking up data | Excel Worksheet Functions | |||
Data Table Question? | Excel Discussion (Misc queries) | |||
Data Filtering question. | Excel Worksheet Functions | |||
VBA Question / Clearing out All Data | Excel Worksheet Functions | |||
Data Validation Question | Excel Programming |