Check letter capitalisations

[abdulhaque]

Hi all,

Is it possible to conduct the following checks in the order listed?

1. Is the first character a number or letter, if it's a letter, is it capitalised? If it's not capitalised, it needs review.
2. Is any character after any space capitalised and the following character not capitalised nor it's a symbol? Needs review

Essentially I need the results to look like the following example. The red font means the cell needs review, whereas the blue font means its ok.

 

[wideboydixon]

By your rules, "A - Alert" should be "Review". Also, you'll need a UDF to do this I think.

Book1
	A	B
1	Strong	Correct
2	Doppler only	Correct
3	1	Correct
4	4-6 steps	Correct
5	PCA pump	Correct
6	possible delirium +/- cognitive impairment	Review
7	Not Applicable	Review
8	Form-mashed €	Correct
9	Nil By Mouth	Review
10	Grade 1 (Very mildly thick)	Correct
11	1L fluid restriction	Correct
12	A - Alert	Review
13	2/3 Story	Review
14	Advised To Attend A&E	Review
Sheet1

Cell Formulas
Range	Formula
B1	=ReviewText($A1)

Public Function ReviewText(testString As String) As String

Dim words As Variant
Dim word As Long

If Left(testString, 1) >= "a" And Left(testString, 1) <= "z" Then
    ReviewText = "Review"
    Exit Function
End If

words = Split(testString, " ")
For word = 1 To UBound(words)
    If Len(words(word)) > 1 Then
        If Left(words(word), 1) >= "A" And Left(words(word), 1) <= "Z" _
        And Mid(words(word), 2, 1) >= "a" And Mid(words(word), 2, 1) <= "z" Then
            ReviewText = "Review"
            Exit Function
        End If
    End If
Next word

ReviewText = "Correct"

End Function

WBD

 

[abdulhaque]

By your rules, "A - Alert" should be "Review". Also, you'll need a UDF to do this I think.

Public Function ReviewText(testString As String) As String

Dim words As Variant
Dim word As Long

If Left(testString, 1) >= "a" And Left(testString, 1) <= "z" Then
    ReviewText = "Review"
    Exit Function
End If

words = Split(testString, " ")
For word = 1 To UBound(words)
    If Len(words(word)) > 1 Then
        If Left(words(word), 1) >= "A" And Left(words(word), 1) <= "Z" _
        And Mid(words(word), 2, 1) >= "a" And Mid(words(word), 2, 1) <= "z" Then
            ReviewText = "Review"
            Exit Function
        End If
    End If
Next word

ReviewText = "Correct"

End Function

WBD

Thanks a lot. The power of VBA works wonders.

 

[Rick Rothstein]

Thanks a lot. The power of VBA works wonders.

I believe this slightly more compact UDF will also work for you...

Function ReviewText(ByVal S As String) As String
  If S Like "[!A-Z0-9]*" Or S Like "?* [A-Z][a-z]*" Then
    ReviewText = "Review"
  Else
    ReviewText = "Correct"
  End If
End Function