Microsoft Excel - Change Text to Sentence Case

How to Convert Text into Sentence Case Using an Excel VBA Function

Sep 6, 2009

One function missing from Excel's built-in library is a method to convert text into sentence case. This article contains a simple VBA function for this purpose.

Microsoft Excel has built in functions for converting text to upper, lower and proper case. All of these are particularly useful when manipulating cells containing text. However, there are occasions where it is convenient to change text into sentence case. There are a variety of methods for achieving this, however the easiest and by far the most effective, is to create a user-defined VBA (Visual Basic for Applications) function that can be called in the same way as any other Excel function.

Example Sentence Case Function

Function SENTENCE(text As String)

'Function converts text in Excel cell into sentence case

Dim newText As String, CharToAdd As String

Dim nextCharUCase As Boolean

newText = ""

nextCharUCase = True

For i = 1 To Len(text)

CharToAdd = LCase(Mid(text, i, 1))

If CharToAdd = "." Then

nextCharUCase = True

ElseIf nextCharUCase = True And (CharToAdd Like "[a-z]" Or CharToAdd Like "[0-9]") Then

CharToAdd = UCase(CharToAdd)

nextCharUCase = False

End If

newText = newText & CharToAdd

Next i

SENTENCE = newText

End Function

Function Explanation

The function works by using a for loop to loop through every character in the string, if a character is a period (full stop), it sets a boolean to true. If the boolean is set to false the character will be set to lowercase, if it's set to true the character will be converted to upper case and the boolean value reset to false for the following character.

How to use the Function

To use the function it should be inserted into a module within the workbook. This can be done within the Visual Basic Editor.

Once the function exists it can be called in the same way as any Excel function. Possible methods for calling a function include:

The function accepts a string parameter, this can either be a cell containing text or a string itself. For example, to convert the text “an example text”, either of the following methods are acceptable:

Sentence Case Limitations

The function is limited to converting the character following a period (full stop) to uppercase and all other characters to lower case.

Obviously there are occasions where this is not entirely accurate, for example, the use of proper nouns should always start with a capital letter, as should letters in acronyms. As the function has no way of distinguishing a noun or an acronym from a normal word it will convert these to sentence case.

The copyright of the article Microsoft Excel - Change Text to Sentence Case in Computer Programming is owned by Katie Giles. Permission to republish Microsoft Excel - Change Text to Sentence Case in print or online must be granted by the author in writing.