0

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)?

Community
  • 1
  • 1
Marcelino Velasquez
  • 85
  • 10
  • What type of cell content are you working with? Single words, or multi-word text? – Tim Williams Jan 05 '21 at 00:18
  • Most of the time single words, but some cells may have multiples words in them like 2 or 3. The code should work however so that if a recognized abbreviation is found in the cell, that abbreviation will be converted to UpperCase while all other words are Proper. – Marcelino Velasquez Jan 05 '21 at 00:22
  • Single words would be relatively straightforward, as shown in Excel Hero's post below, but multiple words would be more difficult unless there's some predictable separator you can use to test each word by itself against your list of upper-case terms. – Tim Williams Jan 05 '21 at 00:31
  • I believe the single word solution Excel Hero Provided will do for now. I'll have to confirm if a multi-word implementation is needed and if so, I'll be sure to revisit this page once again if I have trouble with that. Thanks so much guys! – Marcelino Velasquez Jan 05 '21 at 00:41

1 Answers1

1

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.

Excel Hero
  • 14,253
  • 4
  • 33
  • 40
  • Thanks Excel Hero. Your solutions works well for single words in cells, and I believe this will suffice for my needs. If it turns out I need a multi-word implementation and I have trouble implementing that, I'll be sure to come back to this page. – Marcelino Velasquez Jan 05 '21 at 00:42
  • You are most welcome. This solution works for multiple words in a cell as well. I tested it with multiple words in cells before I posted it. You could possibly have a conflict if you have periods in cells, but then you could just use pipes (|) or some other unique character in the EXCEPTIONS string. – Excel Hero Jan 05 '21 at 01:05
  • Apologies, I meant *single abbreviations/exceptions in cells. I'll definitely try pipes if the code needs such tuning! – Marcelino Velasquez Jan 05 '21 at 01:08