CODEWITHSUNDEEP
excelvbaexcel-formulaconditional-formatting
Marcelino Velasquez
Marcelino Velasquez

Reputation: 87

Excel Conditional Proper Case Formatting

I am trying to convert text in cells to Proper Case format, except certain abbreviations (lets say "DAD", "ABC", "CBD"), which should be Upper Case.

From these links for Proper Case Conversion and Conditional Formatting, I need to use the Select Case statement, but I am not sure how to implement it.

Sub ProperCase()
Dim Rng As Range
Dim WorkRng As Range

On Error Resume Next
xTitleID = "Conditional Proper Case Conversion"

Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleID, WorkRng.Address, Type:=8)

For Each Rng In WorkRng
    Rng.Value = Application.WorksheetFunction.Proper(Rng.Value)
Next
End Sub

This code requests a range of cells to perform the conversion on.

How do I add the conditional functionality for certain strings/text (i.e. abbreviations)?

Upvotes: 0

Views: 435

Answers (1)

Excel Hero
Excel Hero

Reputation: 14764

This should do it:

Sub ProperCase()
    Dim r As Range
    Const EXCEPTIONS$ = ".dad.abc.cbd."
    
    On Error Resume Next
    For Each r In Application.InputBox("Range", "Conditional Proper Case Conversion", Selection.Address, Type:=8)
        If InStrB(EXCEPTIONS, "." & LCase(r) & ".") Then
            r = UCase(r)
        Else
            r = WorksheetFunction.Proper(r)
        End If
    Next
End Sub

Just edit the EXCEPTIONS constant. Make sure that a period straddles every item in the EXCEPTIONS string.

Upvotes: 1

Related Questions