letters

[jbaldwinroberts]

what is the symbol in excel for a letter and for a number. e.g. to check that a user input began and ended with a letter and that the secong digit was a number etc.
thanks joe

[jbaldwinroberts]

sorry i meant in vba

e.g. for this code

Private Sub TextBox9_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
TextBox9.Text = Trim(TextBox9.Text)

Dim strPhone As String
strPhone = Replace(TextBox9.Text, " ", "")

If Len(strPhone) = 6 Or 7 And InStr(1, strPhone, "here i want to put the symbol for a letter or integer") = 1 Then
strPhone = Mid(strPhone, 1, 4) & " " & Mid(strPhone, 5, Len(strPhone) - 4)
TextBox9.Text = strPhone

Else
MsgBox TextBox9.Text & " is not a valid post code. Please enter a valid post code."
TextBox9.Text = ""
TextBox9.SetFocus
Cancel = True
End If

End Sub

i hope this makes it abit easier to understand what i want to do.

[RobDog888]

Use either IsNumeric or evaluate the character at the desired position for its acceptable ASCII character code. If its certain ASCII values then you know its a number or char or symbol etc.

[jbaldwinroberts]

i cant use isnumeric because it has both in and i need to check that each character is in the right place. how do i use the method you mentioned?

[RobDog888]

Using Mid$ and Instr you can parse each character for evaluation using a select case of valid ascii numbers.

select case ASC(somecharacter variable)
    case 48 To 57 '0-9
        'valid
    case 65 To 90 'A - Z uppercase
        'vaild
    case 97 To 122 'a-z lowercase
        'valid
    case else
        'invalid
end select

[jbaldwinroberts]

back to the isnumeric thing, is it possible to use it in the instr function e.g. like this

InStr(1, strPhone, isnumeric) = 1

thanks joe

[Siddharth Rout]

Hi

I don't know why but I always prefer Mid function to Instr

Does this help?

Dim str As String
 
str = "1aa"
 
'syntax of Mid in layman language is
'mid(string , start position , Length from start position)
If IsNumeric(Mid(str, 1, 1)) Then
    MsgBox "Number"
Else
    MsgBox "Text"
End If

InStr(1, strPhone, isnumeric) = 1

If you want to check if the 1st character in the above string is 1 then this should help...

Dim str As String
 
str = "1aa"
 
If Mid(str, 1, 1) = 1 Then
    MsgBox "The First Character is 1"
Else
    MsgBox "The First Character is not 1"
End If

Hope this helps...

[jbaldwinroberts]

i will use this method if i cant match up the user inputted postcode to a list of postcodes in another sheet