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.
Convert text strings to proper case with exceptions by using formula
Convert text strings to proper case with exceptions by using VBA code
Convert text strings to proper case with exceptions by using formula
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.
Convert text strings to proper case with exceptions by using VBA code
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!
