Proper Case up to first instance of ","

Question

Totally stumped on this, I want to convert the Names in a column to proper case but I do not want to change there titles.

How to convert to proper case up to the first ,

BOB FEGESON
Sally Ran, Ph.D.
GREG HYMAN, MA, CPCC

I Get

Bob Fegeson
Sally Ran, Ph.d.
Greg Hyman, Ma, Cpcc

Want

Bob Fegeson
Sally Ran, Ph.D.
Greg Hyman, MA, CPCC

Thanks

This converts to proper case if If InStr(cell.Formula, ",") > 0

Sub FindChr()
Dim rAcells As Range
Dim rLoopCells As Range
Dim lReply As Long
Dim myRange As Range
Dim cell As Range

'Set variable to needed cells
Set rAcells = Range("D2", Range("D" & Rows.Count).End(xlUp))
Set rAcells = rAcells.SpecialCells(xlCellTypeConstants, xlTextValues)

Set myRange = Range("D2", Range("D" & Rows.Count).End(xlUp))

For Each cell In myRange
    If InStr(cell.Formula, ",") > 0 Then

    "Cant Not Figure out what goes here"

    Else
        ' Convert to Proper Case
      For Each rLoopCells In rAcells
          rLoopCells = StrConv(rLoopCells, vbProperCase)
      Next rLoopCells
    End If
  Next cell
End Sub

Siddharth Rout · Accepted Answer · 2014-02-23T18:46:36.210

1

Try this

Sub FindChr()
    Dim ws As Worksheet
    Dim myRange As Range, cell As Range
    Dim tmpString As String
    Dim MyString As Variant
    Dim i As Long

    '~~> Change this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        Set myRange = .Range("D2", .Range("D" & .Rows.Count).End(xlUp))

        For Each cell In myRange
            If InStr(1, cell.Formula, ",") Then
                MyString = Split(cell.Formula, ",")

                tmpString = StrConv(MyString(0), vbProperCase)

                For i = 1 To UBound(MyString)
                    tmpString = tmpString & "," & MyString(i)
                Next i

                cell.Formula = tmpString
            Else
                cell.Formula = StrConv(cell.Formula, vbProperCase)
            End If
        Next cell
    End With
End Sub

enter image description here

edited Feb 23 '14 at 18:46

answered Feb 23 '14 at 18:38

Siddharth Rout

147,039
17
206
250

I noticed that I am also getting Betty H.whitney and Hank O'day where I should have Betty H.Whitney and Hank O'Day`. Thanks – – xyz Feb 23 '14 at 19:37
I see that you have marked this as an answer. Does that mean that you have managed to achieve what you want as mentioned in the above comment? If not then try this. After you get `MyString(0)`, simply check for `'` or `.` and then split it again and the concatenate them after you have changed them to propercase. – Siddharth Rout Feb 23 '14 at 21:38
No, sorry, that is me being less than clear, although you answer the question as I wrote (thank you) it. I will try your suggestions. – xyz Feb 23 '14 at 22:26
Siddharth this worked `cell.Formula = Application.WorksheetFunction.Proper(cell.Formula)` in place of `cell.Formula = StrConv(cell.Formula, vbProperCase)`. Thank you again for the Macro. – xyz Feb 24 '14 at 03:04

Proper Case up to first instance of ","

1 Answers1

Linked