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