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:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!