Hi David,
Welcome to the board, David! Not only are you a first-time poster, but I'm about to become a first-time solution attempt poster! How befitting!
The previous 2 suggestions are a good start. So, I propose a 2-part solution, if it might work for you. I'm using Excel 2007. Please forgive me if I provide too much detail, as I don't know your level of Excel expertise, and it may prove to be useful to others.
Let's use column B for user entry and Column A to return the final coerced value. Column A could be 'hidden' if you don't want your users to see it and become confused. However, you must have an empty column immediately to the left of the user input column (Column B) to receive the final 'non-text' values. When you finally apply this to your production workbook, you may desire to copy any existing 4 digit values in the 'user input' column into Column A first. This solution is for one entry at a time, not a list of values to be pasted into place.
Part 1: Prepare formatting and data validation
- Highlight Column B and format as 'text'
- Select Cell B2, hold Shift key and PgDn as far as you want to go to allow user entries.
- Click Data tab on ribbon bar, select Data Validation > Data Validation
- In Settings, choose Allow: 'Text Length' Leave 'ignore blanks' checked
- Data: 'equal to'; Length: '4'
- Click 'Error Alert' tab. In 'Title' enter: 'Invalid Entry'; in 'Error Message' enter: 'You must enter exactly 4 numerical digits!' (yes, I know that's redundant...we're working with users here!!! ) Click OK
All you have done in Part 1 is ensure a user can only enter exactly 4 characters from the keyboard and prepare the user entry column for input. In Part 2, I'll provide VBA code to further validate the user entry.
Part 2: Worksheet_Change code for input worksheet
Copy all of the following code, open the VB Editor (Alt-F11), under Microsoft Excel Objects, double-click the name of the worksheet (initially a test workbook/worksheet) you're using in the upper left-hand window, paste code into upper right-hand window.
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'The code only works for one cell at a time
'Column B (Column 2) is for user input. The result of the formula below will display in Column A on same row
If Target.Column <> 2 Then Exit Sub 'must be Col B
If Target.Row = 1 Then Exit Sub 'Cannot be Row 1, header row.
Application.EnableEvents = False
If Trim(Target.Value) = Empty Then
Target.Value = "" 'if user deletes entry in target cell
Target.Offset(0, -1).Value = "" 'if user deletes entry, also clear Column A value on same row
GoTo Reset
End If
If IsNumeric(Target.Value) = False Then GoTo Warning 'if entry is not numeric, issue warning
Application.EnableEvents = False
'Since it's possible to add a "+/-" sign in front of 3 digits creating a valid 4 character numeric expression,
'eliminate possiblity
If Left(Target.Value, 1) = "+" Or Left(Target.Value, 1) = "-" Then GoTo Warning
With Target.Cells
.HorizontalAlignment = xlRight 'right align target cell for appearance
End With
If Target.Value < 10000 And Target.Value <> 0 Then 'expression must be 9999 or less, but not 4 zeros
Target.Offset(0, -1).NumberFormat = "0000" 'special formatting for 4 digit result
Target.Offset(0, -1).Formula = Right(Target.Value, Len(Target.Value)) 'don't laugh; it works!
GoTo Reset
End If
Warning:
Application.EnableEvents = False
' If user enters exactly 4 characters, but one or more characters are not numeric,
' warn user and remove their entry attempt
MsgBox ("You must enter exactly 4 numerical digits" _
& vbNewLine & "If the leading digit(s) are zeros," _
& vbNewLine & "You must include them too!" _
& vbNewLine & "Please retry your entry!"), vbCritical
Target.Value = "" 'delete invalid target cell entry
Target.Offset(0, -1).Value = "" 'delete entered value from Column A
Target.Cells.Select 're-select target cell
Reset:
Application.EnableEvents = True 're-enable events
End Sub
BTW: when you are done, make sure you save the workbook as macro-enabled.
Test by entering keystrokes in any cell starting with Cell B2 and going south. Valid results will be captured in Column A, same row.
Note: select any data validated, user input cell in column B. On the ribbon bar, home tab, note the word 'Text' above the 'Number' section. Now, select a cell in Column A that displays a result. Note that the format is 'Special'. Right click on cell, format cells and you'll see it's a Type: ZipCode. Click Cancel. However, I've used the result in formulas in Column E, for instance, as a number; calculations are valid and this formula cell is also becomes type: special. I suspect that data will work downstream from you.
I've tested many combinations of symbols (including +/-), letters, and numbers (including leading zeros) and so far, it has validated correctly. See my additional comments in the code to help explain its logic.
Disclaimer: always test new code on a test workbook first before using in a production environment!
Please let me know if this is an acceptable solution for you and if you encounter any errors you don't expect.
-dougbert
