VBA Express : Multiple Apps - ProperCase with custom delimiters

Multiple Apps

ProperCase with custom delimiters

Ease of Use

Easy

Version tested with

2002

Submitted by:

mgh_mgharish

Description:

The function will convert the given string to the propercase, similar to the StrConv function, but it can also include custom delimiters other than space.

Discussion:

There may be a situation where a sentence is delimited only by comma (,) or a dot (.) etc, in which case StrConv will not make the conversion properly. In that situation, ProperCase works perfectly.

Code:

instructions for use

			Option Explicit 
 
Public Function ProperCase(Source As String, Delimiters As String) 
    Dim s As Variant 
     
     'Copy the source string
    ProperCase = Source 
     
     'Convert each delimiter to a delimiter with space.
    For Each s In Split(Delimiters, " ") 
        ProperCase = Replace(ProperCase, s, s & " ") 
    Next 
     
     'Use built-in function to convert to proper case.
    ProperCase = StrConv(ProperCase, vbProperCase) 
     
     'Convert back to the original form.
    For Each s In Split(Delimiters, " ") 
        ProperCase = Replace(ProperCase, s & " ", s) 
    Next 
End Function

How to use:

Press Alt + F11 to goto VBA.
Insert --> Module.
Paste the code given...
Wherever you need to convert a given string to propercase, just call the function with the first parameter as that string and the next parameter as the delimiters with space between them.
Press Alt + F11 again to come back to the application.

Test the code:

The syntax of the function is ProperCase("Source" , "Delimiters1 Delimiter2") eg =propercase(B2,". - ' ")
dest = ProperCase("vba.express-bringing vba to the world",". -") will make the string dest as "Vba.Express-Bringing Vba To The World"
In the sample Excel Workbook given, type anything in Column A and it will be converted to the propercase in Column B, considering Space, Comma, Dash and Quotes (Double and Single) as delimiters

Sample File:

ProperCase.zip 8.68KB

Approved by mdmackillop

This entry has been viewed 67 times.