Check each word in cell has first capital first letter

[timspin]

Hi All

Can any one help - I have a column K with text descriptions in each cell, I want to check each cell and mark it if any of the words in that cell are not capitalized (not including punctuation).

Any ideas how to do this?

Thanks
Tim

 

[Special-K99]

Probably VBA I would think since you don't want to highlight punctuation.
Do you want to mark them or change them cos if you use PROPER(A1) that would change them and there would be no need to mark them.

 

[Joe4]

Can you post a few examples of what your data looks like?
Are there any numbers?
If there is any punctuation, for any words that appear after that, if there ALWAYS at least one space after the end of the punctuation and before the next word?

 

[Trevor G]

You could use a helper column to identify True or False then conditional format the False element

Helper column would need a formula such as

=EXACT(K2,PROPER(K2))

Then conditional format would be

=$L2=FALSE

 

[Joe4]

Trevor,

You can actually use your solution without having to use a Helper Column. Just amend your Conditional Formatting Formula like this:

=EXACT(K2,PROPER(K2))=FALSE

 

[yky]

Sub test()

Dim cell As Range


For Each cell In ThisWorkbook.Sheets(1).Range("F2:F6") '<- need to supply your own range


If cell.Value <> UCase(cell.Value) Then
cell.Font.Color = RGB(255, 0, 0)
End If
Next


End Sub

 

[Joe4]

yky,
They are looking for Proper Case (first letter of each word capitalized only), not Ucase (which is all Upper Case).

 

[Ombir]

This function will extract words that are not in Proper format separated by spaces in applied Cell:

Function CheckCase(txt As String) As String
Dim sp As Variant
sp = Split(txt)
For Each ele In sp
    If AscW(Left(ele, 1)) < 65 Or AscW(Left(ele, 1)) > 90 Then
        strng = ", " & ele & strng
    End If
Next
CheckCase = Mid(strng, 3)
End Function

 

[yky]

yky,
They are looking for Proper Case (first letter of each word capitalized only), not Ucase (which is all Upper Case).

Thank you. I need to learn how to read.

 

[Rick Rothstein]

This function will extract words that are not in Proper format separated by spaces in applied Cell:

Function CheckCase(txt As String) As String
Dim sp As Variant
sp = Split(txt)
For Each ele In sp
    If AscW(Left(ele, 1)) < 65 Or AscW(Left(ele, 1)) > 90 Then
        strng = ", " & ele & strng
    End If
Next
CheckCase = Mid(strng, 3)
End Function

One possible problem with your function... words within parentheses like "One Two (Three Four) Five".