Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Capitalize first letter in sentences in a string
How to
Howdy All! . . .
The following function capitalizes the first letter in each sentence of a string. The effect is the same as capitalizing [blue]the first letter of the first word.[/blue]
Example:
[blue]"now is. the time. for all good men." & vbNewLine & vbnewline & "to come to the aid of their countrymen."[/blue]
results in:
[blue][purple]N[/purple]ow is. [purple]T[/purple]he time. [purple]F[/purple]or all good men.
[purple]T[/purple]o come to the aid of their countrymen.[/blue]
Be aware, if a sentence doesn't start with text its still the first letter thats capitalized:
[blue]133202qk is a serial number.[/blue]
returns:
[blue]133202[purple]Q[/purple]k is a serial number.[/blue]
Also the following [blue]sentence termination characters[/blue] are incorperated [red]. ? ! : ...[/red] more can be added in the [blue]NextIdx[/blue] function.
Copy/paste the following to a new module in the modules window. Name the module [blue]modCapSentences[/blue]:
Finally a few examples of calling the function:
[blue]Cheers! . . .[/blue]![[thumbsup2]](/data/assets/smilies/thumbsup2.gif "[thumbsup2] [thumbsup2]")
The following function capitalizes the first letter in each sentence of a string. The effect is the same as capitalizing [blue]the first letter of the first word.[/blue]
Example:
[blue]"now is. the time. for all good men." & vbNewLine & vbnewline & "to come to the aid of their countrymen."[/blue]
results in:
[blue][purple]N[/purple]ow is. [purple]T[/purple]he time. [purple]F[/purple]or all good men.
[purple]T[/purple]o come to the aid of their countrymen.[/blue]
Be aware, if a sentence doesn't start with text its still the first letter thats capitalized:
[blue]133202qk is a serial number.[/blue]
returns:
[blue]133202[purple]Q[/purple]k is a serial number.[/blue]
Also the following [blue]sentence termination characters[/blue] are incorperated [red]. ? ! : ...[/red] more can be added in the [blue]NextIdx[/blue] function.
Copy/paste the following to a new module in the modules window. Name the module [blue]modCapSentences[/blue]:
Code:
[blue]Public Function CapSentences(ByVal Txt As String) As String
Dim idx As Long, idxStr As Long, Max As Long, Ltr As String
DoCmd.Hourglass True
idx = 1
Max = Len(Txt)
If Txt <> "" Then
Do
Ltr = UCase(Mid(Txt, idx, 1))
If Ltr >= "A" And Ltr <= "Z" Then
Mid(Txt, idx, 1) = Ltr
Exit Do
Else
idx = idx + 1
End If
Loop Until idx >= Max
idx = 1
Do
'idxStr = InStr(idx, Txt, ".")
idxStr = NextIdx(Txt, idx, Max)
If idxStr Then
Do
Ltr = UCase(Mid(Txt, idxStr, 1))
If Ltr >= "A" And Ltr <= "Z" Then
Mid(Txt, idxStr, 1) = Ltr
idx = idxStr + 1
idxStr = Max
Else
idxStr = idxStr + 1
idx = idxStr
End If
Loop Until idxStr >= Max
Else
idx = Max
End If
Loop Until idx >= Max
CapSentences = Txt
End If
DoCmd.Hourglass False
End Function
Public Function NextIdx(Txt As String, idxLast As Long, Max As Long) As Long
Dim x As Integer, idxBest As Long, idx As Long
x = 1
idxBest = Max
Do
idx = InStr(idxLast, Txt, Choose(x, ".", "?", "!", ":", "..."))
If idx <> 0 And idx < idxBest Then idxBest = idx
x = x + 1
Loop Until x > 5
If idxBest < Max Then
NextIdx = idxBest
Else
NextIdx = 0
End If
End Function[/blue]
Code:
[blue] Me!TextboxName = "sentence one. sentence 2."
Me!TextboxName = CapSentences(Me!TextboxName)[/blue]
Code:
[blue] variablename = "sentence one. sentence 2."
variablename = CapSentences(variablename)[/blue]
Code:
[blue]=CapSentences("sentence one. sentence 2.")[/blue][blue]Cheers! . . .[/blue]