Formatting entry into a cell.

[Hagaar]

Hi there, I know this will be sooo basic for all of you but this is driving me mad.

I want to force the correct entry of a UK driving licence number which is 16 digits. I want it to display in the cell as XXXXX XXXXXX XXXXX The first group of five can be alphanumeric, the next group of 6 must be numbers and the last group of 5 are alphanumeric. I would like a space to show between the groups of numbers for ease of reference.

I hope you can help me. I did try and use the search facility but unless you know how to word the search it does not come up with what I need.

Thanks in anticipation.

 

[jasonb75]

Do you have other information to validate the number against?

Last name, initials, date of birth and gender?

 

[Hagaar]

Hi, I do not need to validate any other data. I just want to make sure that the person entering the number is forced to enter the correct amount of digits and that they are then displayed as xxxxx xxxxxx xxxxx. It’s so simple I can’t believe I can’t find how to do it.

Thank you for your help.

 

[Eric W]

Welcome to the forum!

You can potentially use Data Validation to restrict users from entering bad data. In this example, they will be entering the code in A3. First format A3 as text. Then select Data Validation from the Data tab and enter the custom formula as shown. Displaying it as a 5-6-5 format is trickier, since there isn't a built-in format for that. The best I could do was to use a formula in the B3 cell to display it as you want.

Book1
	A	B
3	abcde123456abcde	abcde 123456 abcde
Sheet21

Cell Formulas
Range	Formula
B3	B3	=LEFT(A3,5)&" "&MID(A3,6,6)&" "&RIGHT(A3,5)

Cells with Data Validation
Cell	Allow	Criteria
A3	Custom	=AND(LEN(A3)=16,ISNUMBER(MID(A3,6,6)+0))

Also note that it's easy to defeat Data Validation. If someone tries to copy and paste a value in the A3 cell, it will accept it. Only manually entered values are checked. You can potentially use VBA (a macro) to provide something a bit stronger.

 

[jasonb75]

Hi, I do not need to validate any other data.

That's not what I asked.

As Eric has pointed out, there are flaws in attempting to validate the format correctly. If you have all of the information that I mentioned then that can be used to validate (or even generate) the first 13 characters accurately.

 

[jasonb75]

See example, although I haven't figured out how to fit this into data validation successfully,

Book1
	A	B	C	D	E	F
1	Last name	First Name	Middle Name	DOB	Gender
2	Smith	John	John	01/02/2003	M	SMITH002013JJ
3	Doe	Jane		10/10/2010	F	DOE99160100J9
Sheet16

Cell Formulas
Range	Formula
F2:F3	F2	=LEFT(SUBSTITUTE(UPPER(A2),"MAC","MC")&999,5)&LEFT(TEXT(D2,"yy"))&TEXT(MONTH(D2)+IF(E2="F",50),"00")&TEXT(D2,"dd")&MOD(YEAR(D2),10)&LEFT(LEFT(B2)&LEFT(C2)&9,2)