All geek questions in one place

Convert to Proposal Case Using VBA

I poisoned page by page on Google and searched for a solution to this seemingly simple request, but to no avail. Does anyone know a reliable way to convert a string to a sentence using vba ?

Ideally, I would build it as a helper, not a function, so it’s easier to call it from the graphical interface.

For reference, I would like to:

HERE LONG, COAL VERSION. PLEASE READ ME IMMEDIATELY.

to become:

Here is a long, ugly title sentence. Change me immediately.

Converting to a title I found it very simple (since there is a built-in function), but converting to a sentence phrase turned out to be really really difficult.

I tried some of the following methods, but every time errors occurred:

How can I make this work?

+6
source share
2 answers

You can use RegExp to run analysis more efficiently.

Something like that

Sub Tested() Call ProperCaps("HERE IS A LONG, UGLY UPPERCASE SENTENCE. PLEASE AMEND ME IMMEDIATELY." & vbCrLf & "next line! now") End Sub Function ProperCaps(strIn As String) As String Dim objRegex As Object Dim objRegMC As Object Dim objRegM As Object Set objRegex = CreateObject("vbscript.regexp") strIn = LCase$(strIn) With objRegex .Global = True .ignoreCase = True .Pattern = "(^|[\.\?\!\r\t]\s?)([az])" If .test(strIn) Then Set objRegMC = .Execute(strIn) For Each objRegM In objRegMC Mid$(strIn, objRegM.firstindex + 1, objRegM.Length) = UCase$(objRegM) Next End If MsgBox strIn End With End Function
+7
source

Thanks for that, a useful bit of code. Why VB has the right case and not a sentence, this is very strange. I changed it for my purpose, because the original will not use the first letter, if there is space in front of it, I hope that you do not mind me sharing my small changes.

To remove unnecessary spaces at the beginning or at the end of a sentence, I added another function that is called from the above.

 Public Function DblTrim(vString As String) As String Dim tempString As String tempString = vString Do Until Left(tempString, 1) <> " " tempString = LTrim(tempString) Loop Do Until Right(tempString, 1) <> " " tempString = RTrim(tempString) Loop DblTrim = tempString End Function Public Function ProperCaps(strIn As String) As String Dim objRegex As Object Dim objRegMC As Object Dim objRegM As Object Set objRegex = CreateObject("vbscript.regexp") strIn = DblTrim(strIn) With objRegex .Global = True .ignoreCase = True .Pattern = "(^|[\.\?\!\r\t]\s?)([az])" If .test(strIn) Then Set objRegMC = .Execute(strIn) For Each objRegM In objRegMC Mid$(strIn, objRegM.firstindex + 1, objRegM.Length) = UCase$(objRegM) Next End If ProperCaps = strIn End With End Function

You can call ProperCaps (Yourstring) to return the offer with the first letter as capital, while all spaces have been removed.

You can also use DblTrim (Yourstring) to remove all spaces at the front and back of the line (without changing the sentence), regardless of the number of spaces.

+1
source

Source: https://habr.com/ru/post/917781/

More articles:


All Articles