# SolvedAccess - Counting Letters and Numbers In A String (1 Viewer)

#### EzGoingKev

##### Registered User.
Good afternoon.

I have some data like this:

AB12
ABC1
ABC12
ABCD123EF

I would like to count letters, numbers, and then letters again if applicable. This would give me:

AB12 = 22
ABC1 = 31
ABC12 = 32
ABCD123EF = 432

I am not sure if this something I can do in a query using LEN along with LEFT/MID/RIGHT/INSTR or if it would require a function be created in VBA.

#### theDBguy

##### I’m here to help
Staff member
I think you're going to need a custom function to do that.

#### The_Doc_Man

##### Immoderate Moderator
Staff member
Note that you did not specify whether your outputs would be numbers or digit strings. I am going to assume that you want digit strings, not an actual number. (But of course if you wanted a number, you could just use a CLng or CInt of the digit string.)

Look here:

The class module described in the link will support this kind of counting if you make one function to encapsulate the operation. Use the instructions in the document in that zip file to add the parser to your project. Then, in a separate general module, add code that does this. (And you can clean up the spacing - I shot this one from the hip so the comments will probably not line up at all.)

Code:
``````Public Function CountStrings( stInput As String ) As String
Dim Prs as New ParserObj
Dim CSResult As String

Prs.OptAN = FALSE       'disallow mixed alphanumeric strings
Prs.OptQS = FALSE       'disallow quoted strings
Prs.ParseLine = stInput       'give parser the input string
CSResult = ""                     'assume no strings
While Prs.AtEOL = FALSE     'while we still have input....
Prs.ParseNext                  'parse another segment
Select Case Prs.TkCode     'check type of string content
Case 1, 2                       'for cases 1 and 2 (alphabetic and numeric digits)....
CSResult = CSResult & CStr( Prs.TkSize )    'tack on the size of the substring
Case Else                      'ignore anything else
End Select
Wend                                    'when done,....
End Function``````

Now when you want this conversion, just use stX = CountStrings( "ABC123DEF" ) and you will get "333" (If I did that right.)

It will also work in a query if the function is declared publicly in a general module.

#### arnelgp

##### ..forever waiting... waiting for jellybean!
Code:
``````'arnelgp
Public Function CountChars(ByVal sText As String) As Long
Dim sTemp As String
Dim ln As Integer
Dim i As Integer, j As Integer
Dim k As Integer, m As Integer
Dim z As Integer
Dim arrNum() As Integer
sText = sText & vbNullString
ln = Len(sText)
If ln < 1 Then Exit Function
ReDim arrNum(0 To 19)
For i = 1 To ln
sTemp = Mid\$(sText, i, 1)
If z < 1 Then
j = 0: k = 1
If IsAlpha(sTemp) Then
z = 1
ElseIf isNumeral(sTemp) Then
z = 2
Else
z = 3
End If
arrNum(j) = k
Else
If IsAlpha(sTemp) Then
m = 1
ElseIf isNumeral(sTemp) Then
m = 2
Else
m = 3
End If

If z <> m Then
j = j + 1
k = 1
Else
k = k + 1
End If
z = m
arrNum(j) = k
End If
Next
ReDim Preserve arrNum(0 To j)
sTemp = ""
For i = 0 To UBound(arrNum)
sTemp = sTemp & arrNum(i)
Next
Erase arrNum
CountChars = CLng(sTemp)
End Function

Private Function IsAlpha(ByVal sTest As String) As Boolean
Const Letter = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
IsAlpha = Letter Like "*" & sTest & "*"
End Function

Private Function isNumeral(ByVal sTest As String) As Boolean
Const Numbers = "0123456789"
isNumeral = Numbers Like "*" & sTest & "*"
End Function``````

Debug.Print CountChars("ABCD123EF")
Result: 432

#### Sun_Force

##### Active member
Code:
``````'arnelgp
Public Function CountChars(ByVal sText As String) As Long
Dim sTemp As String
Dim ln As Integer
Dim i As Integer, j As Integer
Dim k As Integer, m As Integer
Dim z As Integer
Dim arrNum() As Integer
sText = sText & vbNullString
ln = Len(sText)
If ln < 1 Then Exit Function
ReDim arrNum(0 To 19)
For i = 1 To ln
sTemp = Mid\$(sText, i, 1)
If z < 1 Then
j = 0: k = 1
If IsAlpha(sTemp) Then
z = 1
ElseIf isNumeral(sTemp) Then
z = 2
Else
z = 3
End If
arrNum(j) = k
Else
If IsAlpha(sTemp) Then
m = 1
ElseIf isNumeral(sTemp) Then
m = 2
Else
m = 3
End If

If z <> m Then
j = j + 1
k = 1
Else
k = k + 1
End If
z = m
arrNum(j) = k
End If
Next
ReDim Preserve arrNum(0 To j)
sTemp = ""
For i = 0 To UBound(arrNum)
sTemp = sTemp & arrNum(i)
Next
Erase arrNum
CountChars = CLng(sTemp)
End Function

Private Function IsAlpha(ByVal sTest As String) As Boolean
Const Letter = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
IsAlpha = Letter Like "*" & sTest & "*"
End Function

Private Function isNumeral(ByVal sTest As String) As Boolean
Const Numbers = "0123456789"
isNumeral = Numbers Like "*" & sTest & "*"
End Function``````

Debug.Print CountChars("ABCD123EF")
Result: 432
@arnelgp
Code:
``sText = sText & vbNullString``
I've seen so many times you add a null string to a string. What does it mean and what is the benefits?

Many thanks.

#### arnelgp

##### ..forever waiting... waiting for jellybean!
this is simpler:
Code:
``````'arnelgp
Public Function CountAlphaNumeric(ByVal sText As Variant) As Long
Dim var As Variant
Dim v As Variant
Dim tmp As String
sText = sText & vbNullString
If Len(sText) < 1 Then Exit Function
tmp = "0"
With CreateObject("VBScript.RegExp")
.Pattern = "([0-9]+)|([a-zA-Z]+)"
.Global = True
.IgnoreCase = True
.MultiLine = True
Set var = .Execute(sText)
For Each v In var
'Debug.Print v
tmp = tmp & Len(v)
Next
End With
CountAlphaNumeric = Val(tmp)
End Function``````

• Gasman

#### arnelgp

##### ..forever waiting... waiting for jellybean!

Public Function CountChars(ByVal sText As Variant) As Long

i concat it to vbNullString (""), since a Null value can be passed (in the case of field value).

• Sun_Force

#### pbaldy

##### Wino Moderator
Staff member
I did this yesterday, never hit post. I post it now for the regex challenged (me included):

Code:
``````Public Function CountChar(varInput As Variant) As Variant
Dim strString               As String
Dim x                       As Integer
Dim booNumeric As Boolean
Dim intCurCount As Integer

If Len(varInput & vbNullString) > 0 Then
booNumeric = IsNumeric(Mid(varInput, 1, 1))   'establish starting value

For x = 1 To Len(varInput)
If booNumeric = IsNumeric(Mid(varInput, x, 1)) Then
intCurCount = intCurCount + 1
Else
strString = strString & intCurCount
booNumeric = IsNumeric(Mid(varInput, x, 1))
intCurCount = 1
End If
Next x
strString = strString & intCurCount
End If

CountChar = strString
End Function``````

#### EzGoingKev

##### Registered User.
Thanks for the replies everyone.

Doc - I imported the class module and put the code from your reply in a module. The "Return CSResult" turned red as soon as I pasted it.

I wound up using pbaldy's solution and it worked great.

Thanks again for the help.

#### pbaldy

##### Wino Moderator
Staff member
I wound up using pbaldy's solution and it worked great.

Happy to help!

That line in Doc's code was not actual code. It would be:

CountStrings = CSResult

#### Galaxiom

##### Super Moderator
Staff member
I would really encourage you to all read this post very carefully and look at the demo. I explain a fundamental technique that I hope will change the way you think about problems like this.
I am not sure if this something I can do in a query using LEN along with LEFT/MID/RIGHT/INSTR or if it would require a function be created in VBA.
Generally most developers confronted by this actually quite complex problem would make a custom function as have the other posters. The problem with these techniques is that all the data is fed through what is effectively a tiny hole, a single character at a time.

Arnelg's Regular Expression is no exception, it just has the tiny hole hidden below the surface in the Regex. We discovered this a while back when Isladogs compared RegEx against custom VBA functions after I suggested RegEx in a solution. RegEx is actually slower, probably because it is so powerful and is able to recognise a vast variety of patterns. rather than be focused on the simple pattern required.

These techniques are what is known by database professionals as RBAR (pronounced "re-bar") which means "Row By Agonising Row". It isn't efficient because it doesn't make use of the database engine's ability to process data as sets and it has to call the function which is interpreted code not like the native functions in SQL. Consequently, while the function works well on small numbers of records it is relatively slow on a large dataset.

The database I have attached shows how to do this task using a query that only employs database engine capabilities to get the results. It is not an especially pretty example of the technique. I just threw together something that worked in this situation. I expect it could be nicer if I gave it some more thought (or used TSQL). I would also normally write this as embedded subqueries but it is much easier to see how it works in stages.

The crux is the query GetChar which uses what is called a Tally table. A Tally table is just a set of records with integers. (There are several ways to manifest them without storing a table but this one is just a simple table.) Using a Cartesian Product (ie no join) to the data, the Tally table is used to drive Mid() , returning a record for every character in the data along with its recordID and its position in the record.

Basically this process completely shreds the data and tags it. This is not an intuitive thing for a human to do trying to solve this problem but it is the way database engines function best. Lots of records, few columns and simple relationships.

The rest of the queries just manipulate the data into the form we need. It is complicated a little by the variable number of groups.

GetAlpha and GetNum separate the Alpha and Numeric records from GetChar using Like "[0-9]" for Numerals and Like "[a-z]" for characters.

GetLastAlpha and GetLastNum find the last alpha and numeric characters in the groups by checking if the subsequent character in a record is the opposite type. It can only be viewed in SQL because of the join type is not supported in the designer. They then Union the last Alpha and Numeric characters in the string (respectively) because they are not followed by an opposite type. GetLastAlpha also Unions a Null for each record. Without it, none of the two group results are returned in the subsequent query. It is much simpler than dealing with what I expect would be some really awful logic.

Results puts the combinations of character positions back into single records. The two group and three group variations of those with three groups are both returned. A more general solution with an arbitrary number of groups might use a Crosstab but it wouldn't be straightforward.

Final rejects the two group results for those with three groups, calculates the Answer and joins back to the data so it can be displayed with the results.

I have not clocked it against the VBA functions but I have little doubt it would hugely outperform the functions on large numbers of records. Anyone care to add a heap of records and import the functions to see? I need to go to bed.

#### Attachments

• CountChars.mdb
304 KB · Views: 48
• pbaldy and Minty

#### The_Doc_Man

##### Immoderate Moderator
Staff member
My bad... that syntax (RETURN value) only applies to VB, not to VBA. If I recall, I threw that function together after midnight.

pbaldy supplied the correct response for VBA.

#### EzGoingKev

##### Registered User.
No worries.

I am not a developer. 95% of the what I can do I learned on the fly to resolve an issue.

If I can copy/paste something and it works then great. If it does not work I look at it. In some cases I understand the coding and can trouble shoot. In most cases I cannot.

##### CID VIP
Until prompted by @Galaxiom today, I'd completely forgotten about running a speed test comparing regex with a different approach.
see Range of ASCII values in criteria | Page 2 | Access World Forums (access-programmers.co.uk) - posts #23 onwards

I've never really got to grips with using Regex and find it fairly challenging.
Undoubtedly, Regex is very powerful but it is also comparatively slow.

if if have time, I'll compare the speed of each approach used here: VBA by @pbaldy, @arnelgp's Regex version & the use of queries suggested by @Galaxiom and report back.
Luckily I have my own code for generating long lists of random alphanumeric strings

Last edited:

Replies
13
Views
318
Replies
20
Views
415
Replies
4
Views
183
Replies
25
Views
610
Replies
4
Views
283