Excel Excel 2010 How to prevent users typing in full stops in cell

C

clayts

Joined
Jan 30, 2012
Messages
3
Reaction score
0
I'm trying to stop users typing in full stops into a cell, which otherwise is text only.

I understand about customising Data Validation, but am struggling to come up with a formula which prevents users typing in "." but still allows them to use spaces.

If anyone could assist I would be most grateful
 
clayts,

Can you give more detail on the workflow? What are the users entering, what are you doing with the data, why is it important that the cell not contain a period?

The more the better, I will try to help you.

Stoneboysteve
 
Sure - it's basically a column for people to enter surnames only, so it's text only. What some people are doing is typing in initials before those surnames with a full stop after it, eg B. Smith instead of Smith. I do need to have the capacity for spaces as some surnames are two names, eg Bonna Valle

I basically want to stop them entering full stops only (Clearly if they decide they don't want to use full stops I won't be able to control that, so this is the next best thing)

Thanks for your attention
 
If all you are worried about is removing the period, and I would assume the character preceding it- in your example of B. Smith I assume you would rather remove the B and the period, I would use the text to columns command and select only the period as the delimiter. Then you would have a single column of surnames.

Stoneboysteve
 
The idea is try to avoid having to do text to columns, which is of course the solution I currently have to resort to, which creates unnecessary additional work for me.

The ideal solution is to prevent entry in the first place - it seems Excel 2010 prevents me using the tricks that would have worked under previous versions of Excel :(
 
clayts,

I made a macro that will remove all periods when a change is made to the worksheet. This means if the user hits enter, tab, clicks a cell with the mouse, etc.

All periods on the worksheet are affected.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Every time someone enters a period and changes the worksheet the period will be removed.
' Add to the worksheet change event.
'select alt>F11 to open up VB
'in VB explorer expand Microsoft Excel Objects
'Select the sheet you wish to apply the code
'Paste this code in the code area
'Save and close.

'stoneboysteve

Cells.Replace What:=".", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

End Sub​


Stoneboysteve
 
Hi clayts,

You can use the following VBA function to validate the data:

Function Surname(MyInput As String) As Boolean
Surname = True
Dim MyCount As Integer
For MyCount = 1 To Len(MyInput) Step 1

If Asc(Mid(MyInput, MyCount, 1)) > 63 And _
Asc(Mid(MyInput, MyCount, 1)) < 123 Then
Else
If InStr(" '-", Mid(MyInput, MyCount, 1)) > 0 Then
Else
Surname = False
End If
End If

If Surname = False Then
MyCount = Len(MyInput)
End If

Next MyCount

End Function

Unfortunately you can't use user defined functions in Custom Data Valaidation but there is a work around.
If your data is in A1, enter the following in B2 (or any
other cell): = Surname(A1)

Position your cursor on A1 and go to Data, Data Validation and pick Custom under allow and enter =B2 under formula.

The function will evaluate the entry of data and return either a true or false. If False, the data validation will fail and you can display an approitae message (Enter message on Data Validation window under Error Alert Tab).

I've allowed for the following characters in the surname: Space - and '. You can expand this list in the code in the instruction in the list after InStr:
If InStr(" '-", Mid(MyInput, MyCount, 1)) > 0 Then

Hope this helps.

Regards

Fair Cape:)
 
In Data Validation, select Custom and enter the following formula:
=IF(IFERROR(FIND(".",A5),0)>0,1,0)=0
If you want, you can also customise your error message within Data Validation with something like this:
Please do not use "." (Full Stops) in your Name. Use (Space) instead.
 
Back
Top