434,905 Members | 2,083 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,905 IT Pros & Developers. It's quick & easy.

# How many time a string appears in a text

 P: n/a How can I find how many time a string appears in a text. Is there a function in access that does it? For example, text: "My name is John Smith, and my wife is Katy Smith" HowMany(text, "Smith") this will give me 2. Thanks, galsaba Nov 13 '05 #1
5 Replies

 P: n/a Here is a function that you can use: Public Function CountStringOccurrence(ByVal sStr As String, ByVal sChar As String, _ Optional yCase As Boolean = True) As Long ' Ken Snell 3 May 2005 '**THIS FUNCTION COUNTS THE NUMBER OF TIMES A TEXT SUBSTRING IS FOUND IN A TEXT STRING. THE '**SEARCH CAN BE CASE-SENSITIVE (yCase is True) OR CASE-INSENSITIVE (yCase is False). '**THE FUNCTION'S VALUE IS THE NUMBER OF TIMES THE TEXT SUBSTRING IS FOUND. ' intC is used as a loop counter ' sChar is the text substring for which the search is to be done ' sStr is the text string that is to be searched ' yCase is the "case sensitivity" flag (True for case sensitivity; False for case ' insensitivity) Dim intC As Long, intLen As Long CountStringOccurrence = 0 intLen = Len(sChar) If yCase = False Then 'user desires case insensitivity, so make everything Upper Case sStr = UCase(sStr) sChar = UCase(sChar) End If For intC = 1 To Len(sStr) 'if character is found, increment the counter If Mid(sStr, intC, intLen) = sChar Then CountStringOccurrence = _ CountStringOccurrence + 1 Next intC End Function -- Ken Snell "galsaba" wrote in message news:11*********************@z14g2000cwz.googlegro ups.com... How can I find how many time a string appears in a text. Is there a function in access that does it? For example, text: "My name is John Smith, and my wife is Katy Smith" HowMany(text, "Smith") this will give me 2. Thanks, galsaba Nov 13 '05 #2

 P: n/a Function HowMany( _ Optional ByVal String1, _ Optional ByVal String2, _ Optional ByVal Compare As VbCompareMethod = vbBinaryCompare _ ) As Integer Dim intStart As Integer Dim intCount As Integer Dim intInstr As Integer intCount = 0 intStart = 1 intInstr = InStr(intStart, String1, String2, Compare) Do While intInstr > 0 intCount = intCount + 1 intStart = intInstr + Len(String2) intInstr = InStr(intStart, String1, String2, Compare) Loop HowMany = intCount End Function -- Terry Kreft MVP Microsoft Access "galsaba" wrote in message news:11*********************@z14g2000cwz.googlegro ups.com... How can I find how many time a string appears in a text. Is there a function in access that does it? For example, text: "My name is John Smith, and my wife is Katy Smith" HowMany(text, "Smith") this will give me 2. Thanks, galsaba Nov 13 '05 #3

 P: n/a "galsaba" wrote in news:1115131189.653532.73100 @z14g2000cwz.googlegroups.com: How can I find how many time a string appears in a text. Is there a function in access that does it? For example, text: "My name is John Smith, and my wife is Katy Smith" HowMany(text, "Smith") this will give me 2. I think I woud use a recursive sub here. Air code ... 'Option Compare Database 'Option Compare Binary Option Compare Text Option Explicit Sub temp() Dim Occurs As Long CountOccurrences "My name is John Mmm Smith, " _ & "and my wife is Katy Smith-Smithm", _ "m", Occurs MsgBox Occurs '10 End Sub Sub CountOccurrences( _ ByVal vInWhat As String, _ ByVal vCountWhat As String, _ ByRef rOccurs As Long, _ Optional ByVal vStart As Long) vStart = InStr(vStart + 1, vInWhat, vCountWhat) If (vStart = 0) Then Exit Sub Else rOccurs = rOccurs + 1 CountOccurrences vInWhat, vCountWhat, rOccurs, vStart End If End Sub I think I might be in the minority here, though. -- Lyle "The aim of those who try to control thought is always the same. They find one single explanation of the world, one system of thought and action that will (they believe) cover everything; and then they try to impose that on all thinking people." - Gilbert Highet Nov 13 '05 #4

 P: n/a The most elegant solution, but not the fastest or most efficient: http://groups-beta.google.com/group/...8bcc0ce3?hl=en I haven't found the best way to do this. James A. Fortune I'm also looking for a "Blue Ocean" strategy for Access. Nov 13 '05 #5

 P: n/a Yes very nice, but the declaration should be (for most purposes) Public Function CountSubstrings(ByVal strIn As String, strFind As String) As Integer -- Terry Kreft MVP Microsoft Access wrote in message news:11**********************@o13g2000cwo.googlegr oups.com... The most elegant solution, but not the fastest or most efficient: http://groups-beta.google.com/group/...8bcc0ce3?hl=en I haven't found the best way to do this. James A. Fortune I'm also looking for a "Blue Ocean" strategy for Access. Nov 13 '05 #6

### This discussion thread is closed

Replies have been disabled for this discussion.