excelvbatextuppercase

How to convert the phrase into sentence case, disregard the UPPERCASE words


I want to convert all the text to "Sentence case", but I need the UPPERCASE words to remain UPPERCASE. I would manually write it if the file wasn't ~50.000 rows.

A string could start with letters, numbers or symbols.

These are some examples of what I want:

A B
247 Metal Bridge Handle 247 Metal bridge handle
ADRIANO Back Cushion ADRIANO back cushion
Quartz And Crystal Quartz 60 Mm Quartz and crystal quartz 60 mm
*** Fully Integrated Dishwasher By Client*** *** Fully integrated dishwasher by client***
_____Lacquered Woods _____ _____Lacquered woods _____
Base unit 2 doors + 2 shelves [L_10CUC] Base unit 2 doors + 2 shelves [L_10CUC]
Hood HILIGHT X - Stainless steel Hood HILIGHT X - stainless steel

I tried some suggested formulas and VBAs but each one give some errors:

=MID(A1,MATCH(65,CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),1)+1,1)

it will find my first letter (then I can easily search for what's on its left and on its right, and make it lowercase) but sometimes it fails: in B3 I was expecting "A".

A B
247 Metal Bridge Handle M
*** Fully Integrated Dishwasher By Client*** F
ADRIANO Back Cushion N

I also can get my first letter with some VBA but if i want to discard symbols I have to type each one:

    Function FirstChar(Stringa As String)
    Application.Volatile
    Dim sStr As String
    Dim i As Long
    
    For i = 1 To Len(Stringa)
    If Not IsNumeric(Mid(Stringa, i, 1)) Then
    If Mid(Stringa, i, 1) <> " " And Mid(Stringa, i, 1) <> "*" Then
    FirstChar = Mid(Stringa, i, 1)
    Exit Function
    End If
    End If
    Next i
    
    FirstChar = CVErr(xlErrNA)
    
    End Function
A B
247 Metal Bridge Handle M
*** Fully Integrated Dishwasher By Client*** F
___ADRIANO Back Cushion _

Anyway is missing the way to keep the UPPERCASE words.


Solution

  • Basic UDF approach:

    Function SentenceCase(ByVal s As String)
        Dim c As String, i As Long, rv As String, first As Boolean, cap As Boolean, capSeq As Boolean
        s = s & " " 'fudge to simplify checking on next character without running off the end of the string
        first = True
        For i = 1 To Len(s) - 1
            c = Mid(s, i, 1)
            cap = c Like "[A-Z]" 'is this a capital?
            If Not cap Then
                rv = rv & c 'pass non-cap
            Else
                If Not IgnoreCaps(s, i) Then
                    rv = rv & IIf(first, c, LCase(c)) 'lower-case if not first capital
                Else
                    rv = rv & c 'pass through
                End If
                first = False 'not the first capital
            End If
        Next i
        SentenceCase = rv
    End Function
    
    'Is letter at position `i` in string `s` preceded by a capital letter,
    '   or followed by a capital letter or underscore?
    Function IgnoreCaps(s As String, i As Long) As Boolean
        IgnoreCaps = Mid(s, i + 1, 1) Like "[A-Z_]"
        If i > 1 And Not IgnoreCaps Then
            IgnoreCaps = Mid(s, i - 1, 1) Like "[A-Z]"
        End If
    End Function
    

    Might need teaks in IgnoreCaps for other use cases...