Answered by:
How to analyse a string of characters in VBA

Question
-
Hi,
I need to know if a list of character contains only letters or if it contains a numeric part, or only numeric.
How can I do that ?
Thanks
Saturday, June 14, 2014 2:34 PM
Answers
-
WLID1966,
At first I thought it could be done with a formula in a custom field, and it probably could, but the formula would be unwieldy. Therefore I developed this little macro to do the job.
Sub chkStrforNumLet()
Dim t As Task
Dim str As String
Dim Num As Boolean, Ltr As Boolean
For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
str = t.Name
Num = False: Ltr = False
For i = 1 To Len(t.Name)
If Asc(str) > 47 And Asc(str) < 58 Then Num = True
If Asc(str) > 64 And Asc(str) < 91 Or _
Asc(str) > 96 And Asc(str) < 123 Then Ltr = True
If Num = True And Ltr = True Then Exit For
str = Mid(str, 2)
Next i
If Num = True And Ltr = True Then t.Text1 = "Mixed"
If Num = True And Ltr = False Then t.Text1 = "Numbers"
If Num = False And Ltr = True Then t.Text1 = "Letters"
End If
Next t
End Sub
If this answers your question, please mark it as the answer.
John
- Marked as answer by WLID1966 Saturday, June 14, 2014 3:54 PM
Saturday, June 14, 2014 3:27 PM
All replies
-
WLID1966,
At first I thought it could be done with a formula in a custom field, and it probably could, but the formula would be unwieldy. Therefore I developed this little macro to do the job.
Sub chkStrforNumLet()
Dim t As Task
Dim str As String
Dim Num As Boolean, Ltr As Boolean
For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
str = t.Name
Num = False: Ltr = False
For i = 1 To Len(t.Name)
If Asc(str) > 47 And Asc(str) < 58 Then Num = True
If Asc(str) > 64 And Asc(str) < 91 Or _
Asc(str) > 96 And Asc(str) < 123 Then Ltr = True
If Num = True And Ltr = True Then Exit For
str = Mid(str, 2)
Next i
If Num = True And Ltr = True Then t.Text1 = "Mixed"
If Num = True And Ltr = False Then t.Text1 = "Numbers"
If Num = False And Ltr = True Then t.Text1 = "Letters"
End If
Next t
End Sub
If this answers your question, please mark it as the answer.
John
- Marked as answer by WLID1966 Saturday, June 14, 2014 3:54 PM
Saturday, June 14, 2014 3:27 PM -
Great!
Thanks a lot John!
Saturday, June 14, 2014 3:55 PM -
WLID1966,
You're welcome and thanks for the feedback.
John
Saturday, June 14, 2014 4:47 PM