Limit data in a cell

[AndrewRossington]

I have a cell in a table in a spreadsheet that I require people to fill in with a postcode. I want to prevent them from entering data that is not in a postcode format.

My question:
Is there an equivalent of MS Access input masking in MS Excel?

 

[AndrewRossington]

Sorry if this is frustrating, mate. I'm not very good with Excel, and I think we've misunderstood each other. I've tried the formula you suggested, but it still allows me to type "adsj446fss" or whatver combination of random text, which is the basis of my problem.

My aim is to limit the data entered in a cell. Limit by length, character and character placement. Typically, people enter something like "22, long road, birmingham". I want to prevent this by only allowing a combination of letters and numbers entered in a certain sequence (according to UK post codes).

For example, a postcode may be "CT12 4RS". If someone enters "CTA2 4RS" I'd like it to reject the data until the it's entered in the correct format.

 

[fairwinds]

Again, this is exactly what I'm trying to help you with.



You are not putting the formula in a cell are you???


-Data menue
-Validation...

A small window shows.

On Settings tab you choose Allow: "Custom" in the upper box.
In the lower box you enter the formula.

On the two other tabs you can specify the input message and the error message.


Please try again!

 

[AndrewRossington]

Ok, I've tried that.

The formula you suggested doesn't allow me to enter a postcode. (or anything else for that matter)

I keep getting the "The value you entered is not valid" message.

 

[fairwinds]

By entering the formula in a cell you can clearly see which codes are accepted and which are not. See exhibit. The accepted returns TRUE, the rejected returns FALSE.

Experiment with the formula until it works for you.

Book1
	A	B	C	D
1	N1 2LL		TRUE
2	NG1 2LL		TRUE
3	NG14 2LL		TRUE
4	NG1X 2LL		TRUE
5	NG1X 2L5		FALSE
6	NG1X 2xx		FALSE
7	NG1X2LL		FALSE
8	NG1X 2LLlongword		FALSE
Sheet1

 

[AndrewRossington]

Thank you very much for this. It's finally working.

The reason I found it doesn't work is that it doesn't allow entry in lower case and as I kept typing in lower to test it, it kept coming back as an error. Is there a way it can allow upper and lower?

 

[fairwinds]

Sure.

I added that on purpose as I assumed it was supposed to be capitals in postcodes.

=AND(ABS(CODE(LEFT(A1))-94)<30,OR(ABS(CODE(MID(A1,2,1))-52)<6,ABS(CODE(MID(A1,3,1))-52)<6),LEFT(RIGHT(A1,4))=" ",ABS(CODE(LEFT(RIGHT(A1,2)))-94)<30,ABS(CODE(RIGHT(A1))-94)<30,LEN(A1)<9)

 

[AndrewRossington]

There seems to be something wrong somewhere, as I did what you said:

Select a cell

data > validation > custom

I copied and pasted your formula in there, but it didn't let me type anything.

I did the same process for a different cell, and it would let me type anything I wanted... this is with the same formula.

I'm guessing it's something with my spreadsheet that's messing it up.

But I got right as far as accepting upper case, so I think I'll leave it at that.

Thanks for all your help, mate.

 

[fairwinds]

The specific formula I posted must go in A1 as that is the cell I reference in the formula.

You can then copy and paste - special - validation to have it transferred to other cells.

 

[AndrewRossington]

Right on