Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'd like to place some validation against a cell in Excel that allows all characters except a "-" (hyphen) and any white space. I've attempted the following but this doesn't seem to work.

=ISERROR((OR(ISNUMBER(SEARCH("-",C14)),ISNUMBER(SEARCH(" ",C14)))))

If someone could point me in the right direction I'd really appreciate it.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

EDIT: USE =LEN(B3)=LEN(SUBSTITUTE(SUBSTITUTE(B3,"-","")," ","")) or =AND(ISERROR(FIND(" ",B3)),ISERROR(FIND("-",B3)))

@chris neilsen-Thank you

I think the right direction is around here. If the formatting is a bit 'off' Just follow the link above.

To expand the usefulness of my answer, I added some information for people who may have similar questions.

Excel Data Validation Examples Custom Criteria

Prevent Duplicates With Data Validation

You can use Excel Data Validation to prevent duplicate entries in a range on the worksheet. In this example, Employee Numbers will be entered in cells B3:B10, and you can prevent the same number from being entered twice..

  1. Select cells B3:B10

  2. Choose Data|Data Validation

  3. Choose Allow: Custom

  4. For the formula in this example, we use the COUNTIF function to count the occurrences of the value in cell B3, in the range $B$3:$B$10. The formula's result must be 1 or 0:

=COUNTIF($B$3:$B$10,B3) <= 1

Limit the Total

Prevent the entry of a value that will cause a range to exceed a set total. In this example, the total budget cannot exceed $3500. The budget amounts are in cells C3:C7, and the formula checks the total in those cells

  1. Select cells C3:C7

  2. Choose Data|Data Validation

  3. Choose Allow: Custom

  4. For the formula, use SUM to total the values in the range $C$3:$C$7. The result must be less than or equal to $3500:

=SUM($C$3:$C$7) <= 3500

data validation 02

No Leading or Trailing Spaces

You can prevent users from adding spaces before or after the text in the entry. The TRIM function removes spaces before and after the text, and any extra spaces within the text.

The formula in this example checks that the entry in cell B2 is equal to the trimmed entry in that cell.

  1. Select cell B2

  2. Choose Data|Data Validation

  3. Choose Allow: Custom

  4. For the formula, enter:

=B2 = TRIM(B2)

data validation 03

No Spaces in Text

Thanks to Jerry Latham for this example.

You can prevent users from adding ANY spaces in a text string. The SUBSTITUTE function replaces each space character " " with an empty string ""

The formula in this example checks that the entry in cell B3 is equal to the entry without spaces.

  1. Select cell B3

  2. On the Ribbon, click the Data tab, then click Data Validation

  3. Choose Allow: Custom

  4. For the formula, enter:

=B3=SUBSTITUTE(B3," ","") >

data validation no spaces

No Spaces in Cell

You can prevent users from adding ANY spaces in the cell -- whether the entry is text, numbers, or a combination of both. Here are two formulas that check for spaces.

Example 1

Thanks to Jerry Latham for this example.

The LEN function counts the number of characters entered in cell B3, and compares that to the number of characters after SUBSTITUTE removes the space characters.

  1. Select cell B3

  2. On the Ribbon, click the Data tab, then click Data Validation

  3. Choose Allow: Custom

  4. For the formula, enter:

=LEN(B3)=LEN(SUBSTITUTE(B3," ",""))

data validation no spaces

Example 2

The FIND function looks for the space character " " and the ISERROR function result is TRUE, if the space character is not found.

The formula in this example checks that the entry in cell B3 is equal to the entry without spaces.

  1. Select cell B3

  2. On the Ribbon, click the Data tab, then click Data Validation

  3. Choose Allow: Custom

  4. For the formula, enter:

=ISERROR(FIND(" ",B3))

data validation no spaces

share|improve this answer
    
Thanks, I'm able to prevent the entry of one specific character but I'm struggling to prevent entry of two seperate characters, in this case a hyphen & a space.. –  aphrek Nov 11 '11 at 15:26
    
Oh... I'll look again for what you can do... –  wizlog Nov 11 '11 at 15:27
2  
The examples are easily extendable to multiple characters - =LEN(B3)=LEN(SUBSTITUTE(SUBSTITUTE(B3,"-","")," ","")) or =AND(ISERROR(FIND(" ",B3)),ISERROR(FIND("-",B3))) –  chris neilsen Nov 11 '11 at 20:35
    
@chrisneilsen - many thanks for this, works a treat, paticularly like the first option, I hadn't though of that. Thanks again. Joe –  aphrek Nov 14 '11 at 9:23
    
how should I now flag this question as answered? Does it go against the original answer or @chrisneilsen ? Thanks –  aphrek Nov 15 '11 at 11:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.