Microsoft Excel – using wildcards with numbers in Data Validation

I was at a client recently and they wanted to limit data input in an Excel column to have a number starting with 4 and then five numbers after it.

I have worked out a way of doing this using Data Validation.

First, select the data range to which the data validation rule is to be applied and format it as text – Home tab – Number group and from the dropdown where it says General select Text.

Then with the data range still selected, go to the Data tab, then in the Data Tools group, select Data Validation. On the Settings tab, from the Allow dropdown, select Custom.

In the Formula box, the formula in this case is =COUNTIF(C1, “4?????), where C1 is the first cell in the selected range and I want the number to be 4 followed by 5 other numbers.

Click OK.

Although it will be formatted as text, if these are part numbers they are never going to have calculations done on them so works well for data input.

Find out more about our Microsoft Excel training in the London area or our Microsoft Excel training in Wiltshire, Somerset, Dorset and Hampshire.

About jdonbavand

I am a trainer of Microsoft Office, Microsoft Project and Crystal Reports. I have called my blog "If Only I'd Known That...." because I hear it so many times in training sessions. In fact, if only I had a £100 for every time someone says "If only I'd known that." ....
This entry was posted in Excel 365, Microsoft Excel, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016 and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s