VBA check if first letter is capital

[Dimitris254]

Hi all,

I would like to go through a column with names and check if the first letter is capital (English characters/alphabet used in the worksheet).

I've found this thread, but i think it's only for 1 string (i want to go through all cells in column B).


Thank you in advance

 

[JLGWhiz]

couple of questions.
1. You want to check only the first Character of the first text string in each cell in column B, or the first character of each text string in each cell of column B.
2. What do you want to do with the results?

 

[mikerickson]

One more question?

Do you have to deal with strings that begin with non-alphabetic characters, like "@thisSite.com"

If so, does "@" or other symbols count as "capitalized"?

 

[Dimitris254]

The column has only last name and no special characters. For example:

Anderson
cook
Wesley

The macro would simply highlight the cell that the last name starts with lower letter. Then i will correct it to capital manually.

 

[63falcondude]

Used the macro recorder. See if this works.

Sub Macro1()
'
' Macro1 Macro
'


'
    Columns("B:B").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=NOT(EXACT(PROPER(B1),B1))"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

 

[mikerickson]

There are a couple of ways to do this.

One would use Conditional Formatting to highlight the cells that have a lower case first character, using the formula =CODE($B1)>96.
Then the user would go a manually correct the entries.

Another would be to use a macro, not to highlight the offending cells, but to correct them.

Sub test()
    Dim newValue As Variant
    Dim i As Long
    With Sheet1.Range("B:B")
        With Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
            newValue = .Value
            For i = 1 To .Rows.Count
                newValue(i, 1) = UCase(Left(newValue(i, 1), 1)) & Mid(newValue(i, 1), 2)
            Next i
            .Value = newValue
        End With
    End With
End Sub

A third way would be to use DataValidation, to prevent the user from typing in an entry that begins with a lower case letter.

 

[Rick Rothstein]

The macro would simply highlight the cell that the last name starts with lower letter. Then i will correct it to capital manually.

Why not let the macro simply correct them for you? Assuming your names are in Column A starting on Row 2 (Row 1 is an assumed header)...

Sub FixNameCasing()
  Dim Addr As String
  Addr = "A2:A" & Cells(Rows.Count, "A").End(xlUp).Row
  Range(Addr) = Evaluate("IF(" & Addr & "="""","""",PROPER(" & Addr & "))")
End Sub

 

[JLGWhiz]

Might as well throw this in too

Sub t()
Dim c As Range
    With ActiveSheet
        For Each c In .Range("B2", .Cells(Rows.Count, 2).End(xlUp))
            txt = StrConv(c.Text, vbProperCase)
            c.Value = txt
        Next
    End With
End Sub

 

[Dimitris254]

Thank you all, all* the provided macros work flawlessly! (*except for 63falcondude's that gives me error in the second command line)

@Rick: the only reason i'd do it manually is so as not add more complexity to the code you had to think of

 

[Peter_SSs]

Something else to consider if the values are being entered manually is to try to force the correct entry in the first place with a Data Validation custom formula applied to column B

=EXACT(B1,PROPER(B1))