idiot proof custom format

[wfarrell]

I'm using Excel 2010 as a simple database to archive church records. I want to be able to enter an identifier in the first cell with the format as follows:

First character is a capital letter followed by two numbers; then a space, dash, space; then three numbers; then a space, dash, space; then four numbers.


For example,I want to enter "D123456789" and have look like "D12 - 345 - 6789"


Since we are having volunteers entering the data, I also want the entry idiot proof in case they put letters where numbers go and vice verca, and if too many, or not enough, characters are entered.


Is this too much to expect from Excel?

 

[DonMinter]

It is definitely possible. You can use "data validation" and "custom formula." If you do it this way, you must break down the text to the individual characters, then verify that the first character is in the appropriate range using "code()." You'll have to pull out individual characters using left(), len(), mid(), right() type of functions.


This formula is going to become very long and cumbersome, but it will work. I would suggest you first look up an ASCII character chart (A is 65 is about all I can remember), and then make sure each text character is within the range for the appropriate characters.


To get you started: Assume the cell being validated is C6. To check that the first character is a capital letter, the following would work:

'=AND(CODE(LEFT(C6, 1))<91, CODE(LEFT(C6, 1))>64)'


This formula basically says, "if the left character code is less than Z+1 and the Left character code is greater than A-1, evaluate to true, and allow the entry." Make sure you enter ONLY capital letters. You'll also want a custom error message to show the proper format of XNN NNN - NNNN where X is a capital letter and N is any numeral 0 to 9.


I suggest you work in a different cell to get the formula working properly, then cut and paste the working formula into the data validation custom formula box in the cell you want to verify. Once you have one working, use the Excel drag function to copy it to every box you need it.


Other things you can try are first checking the len() is 16. You can also break the formula up into smaller pieces, and put it on cells on another sheet, referencing back to the cell to be evaluated, then make sure all of those cells are =TRUE, throwing the error message if they are not. This method will give you a huge "data validation" sheet, but might make it easier to get a working set of validation formulas.


Hope this helps,

Don

 

[robg]

Another option would be to do it in VBA. Right click on the sheet tab and copy/paste the following.

[pre]

Option Explicit
Const TARGETRANGE = "$A$1:$A$10" ' Range with ID's (try named range instead)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim regex As Object
Dim targetValue As String

Set regex = CreateObject("VBScript.RegExp")
regex.Global = False
regex.IgnoreCase = True
regex.MultiLine = False

If Not Intersect(Target, Range(TARGETRANGE)) Is Nothing Then
targetValue = Replace(Target.Value, "-", "") ' remove dashes, if any
regex.Pattern = "^[A-Z][0-9]{9}$"  'format: X999999999

If regex.Test(targetValue) Then
Target.Interior.Pattern = xlNone  ' optional
Target = UCase(Left(targetValue, 3)) & "-" & _
Mid(targetValue, 4, 3) & "-" & _
Right(targetValue, 4)

Else
Target.Interior.Pattern = xlSolid  ' optional
Target.Interior.PatternColorIndex = xlAutomatic  ' optional
Target.Interior.Color = 255  ' optional
End If
End If
End Sub[/pre]

You would need to alter the constant at the top to define the range it should check (a named range would be best).  I added a red highlight to invalid cells, but take those lines out if you don't want them. Change the [code]regex.IgnoreCase

to False[/code] if you require a an upper case letter to start, but the way it is now the user can enter either and it will be converted to upper case automatically.

 

[dan_l]

solicited wisdom:

Sometimes it's better to apply the formatting after the fact.


unsolicited wisdom:

When talking about volunteers, let's just use the term 'user proof'. Let's save 'idiot proof' for people who are actually payed for their efforts