CODEWITHSUNDEEP
excelvbatextuppercase
nigoo_
nigoo_

Reputation: 23

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.

Upvotes: -1

Views: 106

Answers (2)

Tim Williams
Tim Williams

Reputation: 166101

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...

Upvotes: 1

TinMan
TinMan

Reputation: 7759

Shapes.TextFrame2.TextRange is well suited for editing text.

This will correctly handle the casing with the exception of the first word being numeric.

Shapes.TextFrame2.TextRange.ChangeCase msoCaseSentence

Code

I intentionally made the Ranges simplistic, modify them as needed.

Sub Example()
    Application.ScreenUpdating = False
    Dim Editor As Shape
    Set Editor = ActiveSheet.Shapes.AddTextEffect(PresetTextEffect:=msoTextEffect31, Text:=" ", _
        FontName:="+mn-lt", FontSize:=8, FontBold:=10, FontItalic:=msoFalse, Left:=200, Top:=0)
                
    Dim Data As Variant
    Data = Range("A1").CurrentRegion.Columns(1)
        
    Dim CharCode As Long
    With Editor.TextFrame2.TextRange
        Dim r As Long, s As Long, c As Long
        For r = 1 To UBound(Data)
            .Text = Data(r, 1)
            .ChangeCase msoCaseSentence
                
            Rem This was added to handle the first word in the sentence being numeric (e.g. 247)
            For s = 1 To .Sentences.Count
                With .Sentences(s)
                    For c = 1 To .Characters.Count
                        CharCode = Asc(.Characters(c, 1).Text)
                        If (CharCode >= 65 And CharCode <= 90) Or (CharCode >= 97 And CharCode <= 122) Then
                            .Characters(c, 1).ChangeCase msoCaseUpper
                            Exit For
                        End If
                    Next
                End With
            Next
            Data(r, 1) = Editor.TextFrame2.TextRange.Text
        Next
    End With
    Range("B1").Resize(UBound(Data)).Value = Data
    Editor.Delete
End Sub

Result

Original Text Modified Text
2metal Bridge Handle 2Metal bridge handle
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

Upvotes: 0

Related Questions