I want to use an input mask to format cell content

[OB12OLD]

The cell content is to be 0-7 characters in length.
Alpha characters only, i.e., no numeric.
First character must be uppercase.
Remaining characters may be lowercase or blank.

Is that possible to do in MS Excel 2007?

[FDibbins]

Not sure I understand? Why cant you just type it in like that?

[OB12OLD]

The sheet will be filled in by many different users. I want to ensure I won't have to do clean-up.

[Ron Coderre]

I *think* this data validation rule does what you want:

Please Login or Register  to view this content.

Is that something you can work with?

[David A Coop]

Welcome to the forum!

I can't help you with the NO NUMBERS requirement, but you could use DATA VALIDATION to ensure the Text is entered in the required format.

In the Data Validation (on the Data Ribbon) select Custom from the Allow dropdown, and enter the formula =EXACT(A1,PROPER(A1))*(LEN(A1)>8).

This will ensure that the first character is uppercase, and the length is less than 7.

Regards,

David



When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[mehmetcik]

Hi

This code ensure B5 is as you describe.

right click on the file name at the bottom of excel, select view code. Paste this code.

close visual basic

Et Voila.

Please Login or Register  to view this content.

[Ron Coderre]

I tried the PROPER function, too. But, it allows these:
Abc1 Def
Bcd234
12345

Hence, the construct I came up with...which does not allow those.

[OB12OLD]

Hi mehmetcik,

Thanks for the VB code. Works great!
It's been a long time since I've looked at any of that.

How would it need to be modified to work for a range of cells A18 - A27 and E18 -E27?

[mehmetcik]

Try this ammendment. Not tested yet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub

If Intersect(Target, Range("A18:A27")) Is Nothing and Intersect(Target, Range("E18:E27")) Is Nothing Then exit sub

Application.EnableEvents = False

Entry = target.Value
If Entry = "" Then Exit Sub
errorflag = 0
If Len(Entry) > 7 Then MsgBox "String in " & target.address &" is Too Long", vbOKOnly
For Count = 1 To Len(Entry)
If IsNumeric(Mid(Entry, Count, 1)) Then
If Count > 1 Then temp = Left(Entry, Count - 1)
If Count < Len(Entry) Then temp2 = Right(Entry, Len(Entry) - Count)
Entry = temp & temp2

errorflag = 1
End If

Next
If errorflag = 1 Then MsgBox "Letters and Spaces Only in " & target.address, vbOKOnly


Target.Value = Application.Proper(Entry)
Application.EnableEvents = True
End Sub

[OB12OLD]

Hey, mehmetcik!

So far so good, works as advertised!
Much better than my using a list with everything that might possibly be used in the category.

Thank you so much!

[mehmetcik]

This is the tested version.

Please Login or Register  to view this content.