Create Function to Convert Certain Text to Uppercase

**wwconslt** · 02-10-2015, 11:17 AM

Hello,

I'd like to create a Function to convert text to Uppercase conditionally. I would like any letters that are not part of the abbreviations Apt, No, or Unit to be uppercase. There are many variations. "Apt" and "No" may or may not have a period for abbreviation. The letters that need to be Uppercase may appear right after the number, before, or with a space on either side.

Thank you.

Current Desired
Apt 25a Apt 25A
Apt. 25 a Apt 25 A
Apt 30ab Apt 30AB
Apt ab30 Apt AB30
No 25c No 25C
Unit 500e Unit 500E
Unit 500ab Unit 500AB
Unit ab500 Unit AB500

**Marc L** · 02-13-2015, 09:09 PM

PHP Code:


Function UpperCaseAfterFirstWord$(TXT$)
         SPQ = Split(TXT)
         For R& = 1 To UBound(SPQ):  SPQ(R) = UCase(SPQ(R)):  Next
         UpperCaseAfterFirstWord = Join(SPQ)
End Function

Sub Demo()
    With Sheet1.Cells(1).CurrentRegion.Columns(1).Offset(, 1)
        .FormulaR1C1 = "=UpperCaseAfterFirstWord(RC[-1])"
            .Formula = .Value
    End With
End Sub