Force Data Entry Format in Excel

[grnzbra]

Someone, in their infinite wisdom has created a website that connects to a database. This website has a data entry form which has a field into which three pieces of information are to be entered separated by slashes (/). The .sql code in the database then breaks up the three pieces of information and send them to three fields, each in a different table. I assume the code that separates them is dependent on the slash.
I have been tasked with creating an Excel spreadsheet containing a field to take in this same information - also in one field which, as far as I know, is to be imported directly into SQL Server. My experience with this type of thing tells me that the user cannot be depended upon to follow directions and the cells must have data validation rules set. (and they still manage to screw thing up).Is there any way to format this cell to present the user with slashes between which he will enter the three pieces of information? (I vaguely remember this being called a mask but apparently a mask is something else. What might the thing for which I am looking be called?). I've looked at custom formatting but it only seems to allow this type of formatting for numbers and dates.

 

[sxschech]

What about another approach of having three entry fields and then use vba code or excel concatenate functions to combine and output the data. That way don't have to build a lot of logic to identify whether they used a slash or not.

 

[Andrzejek]

It would be helpful to know what data should the user provide between the slashes (/).
Are the 'pieces' pre-defined?
For example, if you need:
[tt]Month / Day / Year[/tt]
then for Month you have Jan, Feb, ..., Dec, etc.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[grnzbra]

Unfortunately, I need to present the data as in the form - one field for the three pieces
The data going into the field is Building Name/Floor/Room. They are going, eventually, to tblBuilding, tblFloor, tblSpace. I believe that the code is already in place to split these three pieces of information out and feed it to the required locations. This needs to feed it into a table which is then used by existing code which would use the slashes to tell it where which piece of information is which.

 

[Andrzejek]

I would assume for any Building Name/Floor/Room entry, you (should) already have a list somewhere with valid Building Names, and for each Building Name - you have a list of valid Floors, and for each combination of Building Name/Floor combinations you have a list of valid Rooms. Right?


BTW - it is really bad process (in my opinion) asking user to provide this data as '/' delimited data, and then have to split it into any useful data in DB.


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[grnzbra]

I don't have any of that information - the user is supplying it.

I agree. It is bad form but that's the way I've been instructed to do it. I've had this type of nonsense happen before.

 

[Andrzejek]

Then I would go with sxschech's suggestion:

Users can type whatever they want in columns A, B, and C (as long as they do not type any slashes), and you concatenate the data in column D

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[grnzbra]

Thanks. I guess the best to suggest that they do it that way. Thank you.

 

[Andrzejek]

You may enhance the input by informing user about Slashes (in columns A, B, or C) with a little VBA:

Option Explicit

Private Sub [blue]Worksheet_Change[/blue](ByVal Target As Range)

Select Case Target.Column
    Case 1, 2, 3
        If InStr(Target, "/") Then
            MsgBox "[red]No Slashes Allowed[/red]"
        End If
    Case Else[green]
        'Who cares...[/green]
End Select

End Sub

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[combo]

An alternative method could be implementation of data validation:
1. allowed: custom formula,
2. entry message: describe what is allowed, it will be displayed as a moveable tooltip when a cell with DV is active
3. error message: message and its style if wrong data.

Assuming that DV refers to data in A1, formula in DV:
1. if empty fields are allowed, as "/bbb/ccc":
[pre]=AND(LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))=2,TRUE)[/pre]
2. if empty fields are not allowed:
[pre]=AND(LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))=2,FIND("/",A1)>1,FIND("/",A1,FIND("/",A1)+1)>FIND("/",A1)+1,LEN(A1)>FIND("/",A1,FIND("/",A1)+1))[/pre]

The DV can be set in other cells with copy-paste or paste special option.

The drawbacks of DV:
- user can leave empty cell,
- user can bypass DV and paste any contents.

combo

 

[combo]

Actually, formula in case (2) above can be simplified and use wildcards, so will be easier to manage:
[pre]=AND(LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))=2,ISNUMBER(MATCH("*?/*?/?*",A1,0)))[/pre]

The first condition in AND requires exactly two slashes, the second finds the pattern in entry string.

combo