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?

 

[fairwinds]

Hi,

You could propably use data validation.

What is the acceptable post code format?

 

[AndrewRossington]

I'm not sure what Data Validation is.

The format, however, would be:

_ = Letter

* = Number

_(_)*(*)(_) *__

The numbers/letters in brackets are variations depending on the postcode region, eg:

N1 2LL
NG1 2LL
NG14 2LL
NG1X 2LL


Did that make any sense?

 

[fairwinds]

Try:

Select A1.

Data - Validation - Allow: "Custom"

And use this formula:

=AND(ABS(CODE(LEFT(A1))-77)<14,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)))-77)<14,ABS(CODE(RIGHT(A1))-77)<14,EXACT(UPPER(A1),A1))

Then you can specify error message e.t.c.

You can then copy this cell to the other locations where you need the validation.

 

[AndrewRossington]

Appologies... I just read this and I realise how incredibly confusing it is.

Basically what I need is to set a format for data input in a column of cells for both text and numbers. This format would be for UK postal codes, and so would need to include text and numbers.

Hopefully that was a little simpler.


EDIT:

I've just tried your suggestion, but the code allows for long strings of text when I'm trying to prevent that.

 

[fairwinds]

Just add that condition to the formula:

=AND(ABS(CODE(LEFT(A1))-77)<14,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)))-77)<14,ABS(CODE(RIGHT(A1))-77)<14,EXACT(UPPER(A1),A1),LEN(A1)<9)

 

[AndrewRossington]

I'm afriad that doesn't work either.

I'm not very clever with long formula's, so I'm not entirely sure what I need.

Thanks for your help though mate.

Is there something as simple as input masking that I could use in Excel?

 

[fairwinds]

Start with something simpler.

Try to get just the validation for length to work. Follow my instructions above and use
=LEN(A1)<9
as formula.

If you can get that to work, you could then try with the longer one.

 

[AndrewRossington]

It's not length that's the problem exactly.

I'm trying to prevent people entering a building address in the box, so I'd like it to only accept postcode formatted text.

Is this possible?

 

[fairwinds]

That is exactly what I am trying to help you with.

Please read through what I have posted and try to follow the instructions. If it does not work, please try to give some feedback as to what goes wrong.