Limit data in a cell

AndrewRossington

Board Regular
Joined
Oct 27, 2005
Messages
114
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?
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

You could propably use data validation.

What is the acceptable post code format?
 

AndrewRossington

Board Regular
Joined
Oct 27, 2005
Messages
114
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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

Board Regular
Joined
Oct 27, 2005
Messages
114

ADVERTISEMENT

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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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

Board Regular
Joined
Oct 27, 2005
Messages
114

ADVERTISEMENT

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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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

Board Regular
Joined
Oct 27, 2005
Messages
114
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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.
 

Forum statistics

Threads
1,141,847
Messages
5,708,934
Members
421,599
Latest member
santosh234

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top