How to convert text string to proper case with exceptions in Excel?
In Excel, you can apply the Proper function to convert text strings to proper case easily, but, sometimes, you need to exclude some specific words when converting the text strings to the proper case as following screenshot shown. This article, I will talk about some quick tricks for solving this job in Excel.
May be the following formula can help you to deal with this task quickly, please do as this:
Enter this formula:
=UPPER(LEFT(A2))&MID(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" "&PROPER(A2)&" "," Of "," of ")," A "," a "),"Is "," is ")," Usa "," USA ")),2,LEN(A2)) into a cell where you want to get the result, and then drag the fill handle to fill this formula, and the text strings have been converted proper case but specific exceptions, see screenshot:
Note: In the above formula, A2 is the cell you want to convert, “Of ”, “A”, “Is”, “Usa” are the normal proper case words after converting, “of ”, “a”, “is”, “USA” are the words you want to exclude from the proper case. You can change them to your need or add other words with the SUBSTITUTE function.
If the above formula is somewhat difficult to understand and change to your need, here, you can also apply a VBA code to finish this task. Please do with the following steps one by one.
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: convert text strings to the proper case with exceptions:
Sub CellsValueChange() 'Updateby Extendoffice Dim xSRg As Range Dim xDRg As Range Dim xPRg As Range Dim xSRgArea As Range Dim xRgVal As String Dim xAddress As String Dim I As Long Dim K As Long Dim KK As Long On Error Resume Next xAddress = Application.ActiveWindow.RangeSelection.Address Set xSRg = Application.InputBox("Original cells:", "KuTools For Excel", xAddress, , , , , 8) If xSRg Is Nothing Then Exit Sub Set xDRg = Application.InputBox("Output cells:", "KuTools For Excel", , , , , , 8) If xDRg Is Nothing Then Exit Sub Set xPRg = Application.InputBox("Cells to exclude:", "KuTools For Excel", , , , , , 8) If xPRg Is Nothing Then Exit Sub Set xDRg = xDRg(1) For I = 1 To xSRg.Areas.Count Set xSRgArea = xSRg.Areas.Item(I) For K = 1 To xSRgArea.Count xRgVal = xSRgArea(K).Value If Not IsNumeric(xRgVal) Then xRgVal = CorrectCase(xRgVal, xPRg) xDRg.Offset(KK).Value = xRgVal End If KK = KK + 1 Next Next End Sub Function CorrectCase(ByVal xRgVal As String, ByVal xPRg As Range) As String Dim xArrWords As Variant Dim I As Integer Dim xPointer As Integer Dim xVal As String xPointer = 1 xVal = xRgVal xArrWords = WordsOf(xRgVal) For I = 0 To UBound(xArrWords) xPointer = InStr(xPointer, " " & xVal, " " & xArrWords(I)) Debug.Print xPointer Mid(xVal, xPointer) = CorrectCaseOneWord(CStr(xArrWords(I)), xPRg) Next I CorrectCase = xVal End Function Function WordsOf(xRgVal As String) As Variant Dim xDelimiters As Variant Dim xArrRtn As Variant xDelimiters = Array(",", ".", ";", ":", Chr(34), vbCr, vbLf) For Each xEachDelimiter In xDelimiters xRgVal = Application.WorksheetFunction.Substitute(xRgVal, xEachDelimiter, " ") Next xEachDelimiter xArrRtn = Split(Trim(xRgVal), " ") WordsOf = xArrRtn End Function Function CorrectCaseOneWord(xArrWord As String, xERg As Range) As String With xERg If IsError(Application.Match(xArrWord, .Cells, 0)) Then CorrectCaseOneWord = Application.Proper(xArrWord) Else CorrectCaseOneWord = Application.VLookup(xArrWord, .Cells, 1, 0) End If End With End Function
3. Then press F5 key to run this code, and a prompt box is popped out to remind you to select the original cells you want to convert, see screenshot:
4. And then click OK, select the cells where you want to output the results in the popped out box, see screenshot:
5. Go on click OK, and in the popup dialog box, select the texts you want to exclude, see screenshot:
6. And then click OK to exit the dialogs, and all the text strings have been converted to the proper case but exclude the specified words, see screenshot: