Use data validation to validate a numeric, text mixture of characters in excel

Question

I need to validate a cell that has a few specific requirements. The LEN(A1)=6 and the composition of the entered values must be as follows an uppercase text character, then a numeric value (one digit),an uppercase text character, then a numeric value (one digit), an uppercase text character, then a numeric value (one digit). Here is an example A1B2C1.

I will be using this field to validate bin numbers in a stock take and they are being manually entered. I know I can validate the length of the input as well as whether it should be text or numeric, but not the specific mix I require.

Thank you

LEN, MID, ISNUMBER & ISTEXT would seem to take care of this. If you want VBA then give regex a try and come back to show some effort if you run into trouble. — , May 18 '17 at 13:22
@Jeeped ISTEXT won't work as the number is part of a string so will show as true. — Hocus, May 18 '17 at 13:30
@Hocus - `=ISNUMBER(--MID(A2, ROW(2:2), 1))` should do it. I was just hoping to see some original effort from the OP. Anything... anything at all. — , May 18 '17 at 13:38
@Jeeped not sure if this is the place to ask, but would you mind running me through that formula, and explain how it works? — Hocus, May 18 '17 at 13:41
@Jeeped I have used the following to try and just validate a numeric cell with 13 characters and I failed =AND((LEN(E3)=13);(ISNUMBER)) It gives a #NAME? After failing on what should have been a pretty easy thought it would be best to ask here. Apologies if I offended you by my lack of effort. — Drikus Theron, May 18 '17 at 13:49
Possible duplicate of [How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops](http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) — Alexander, May 18 '17 at 15:33

Hocus · Answer 1 · 2017-05-18T13:40:27.857

If you plan to use VBA then checking the ASCII code is an option.

to do this you can use

Left([String] , [Amount of Chars])

Mid([String] , [Starting Char] , [Amount of Chars])

Right([String] , [Amount of Chars])

To get each character then use

ASC([Character to Check])

To get the ASCII code and compare that to what you expect.

So 60 to 95 for Upper case characters etc.

Please see some sample code below, not the cleanest but it works.

If Asc(Mid(StringToTest, 1, 1)) >= 60 And Asc(Mid(StringToTest, 1, 1)) <= 95 And _
   Asc(Mid(StringToTest, 2, 1)) >= 48 And Asc(Mid(StringToTest, 2, 1)) <= 57 And _
   Asc(Mid(StringToTest, 3, 1)) >= 60 And Asc(Mid(StringToTest, 3, 1)) <= 95 And _
   Asc(Mid(StringToTest, 4, 1)) >= 48 And Asc(Mid(StringToTest, 4, 1)) <= 57 And _
   Asc(Mid(StringToTest, 5, 1)) >= 60 And Asc(Mid(StringToTest, 5, 1)) <= 95 And _
   Asc(Mid(StringToTest, 6, 1)) >= 48 And Asc(Mid(StringToTest, 6, 1)) <= 57 Then
    Debug.Print "Pass"
Else
    Debug.Print "Fail"
End If

Thank you @Hocus. I have limited exposure to VBA but will give it a go and let you know. — Drikus Theron, May 18 '17 at 13:59
Hi @Hocus. Your solution worked as well. It was a lot more intricate to grasp, but I was able to get it working. Thank you. — Drikus Theron, May 19 '17 at 09:33

score 0 · Accepted Answer · answered May 18 '17 at 15:17

This is another option you can use. Put it into your sheet module and anytime your cell is changed it will validate the format $#$#$# for you. I have it set up to work on cell "A1" but you can change that to any cell you need. Or you can change it to all cells in a specific column or a specific row. hope it helps

Private Sub Worksheet_Change(ByVal Target As Range)
''   Choose any one of these three range methods
'   By Individual Cell
If Replace(Target.Address, "$", "") = "A1" Then GoTo validate '   Change this to your cell range
''   By Any Cell in Column 1
'If Target.Column = 1 Then GoTo validate  '   Change this to your Column
''   By Any Cell in Column 1
'If Target.Row = 10 Then GoTo validate  '   Change this to your Row

Exit Sub


validate:
If Len(Target) <> 6 Then GoTo errorMsg
For i = 1 To 6
     abc = Asc(Mid(Target, i, 1))
        If i Mod 2 = 0 Then
            If Asc(Mid(Target, i, 1)) < 48 Or Asc(Mid(Target, i, 1)) > 57 Then GoTo errorMsg
        End If
    If i Mod 2 <> 0 And Asc(Mid(Target, i, 1)) > 49 And Asc(Mid(Target, i, 1)) < 58 Then GoTo errorMsg
Next i

Exit Sub

errorMsg:
Target.Interior.ColorIndex = 3
MsgBox "Incorrect Format for this cell"
Application.EnableEvents = False
    Target = ""
    Target.ClearFormats
Application.EnableEvents = True
End Sub

Thank you @John-Muggins this was exactly what I needed and works perfectly. — Drikus Theron, May 19 '17 at 09:32

Use data validation to validate a numeric, text mixture of characters in excel

2 Answers2