Clever way of formatting names nicely?

gers1978 · Oct 14th 2015

I have a column with lots of names, and I want to format them nicely using VBA. I've been trying to use formulas in a spare cell using LEFT, RIGHT, MID, FIND, ISERROR, etc, to get the desired result, then copy/paste the resulting formula into my VBA code, but it's getting very unwieldy, long and hard to follow.

Basically the names as it stands can be anything from:

SMITH, JOHN M.
JONES
TAYLOR, CHRISTOPHER (Chris)
BAKER, MIKE
SMYTH, EDWARD E. (Ted)
MARK.MILLER
PAUL PARKER

I want to format them so that they become First Initial. Surname, unless there's only one word, then I want it left as-is. And I need the whole thing converted to Proper case.

So in the example above, the finished result should be:

J Smith
Jones
C Taylor
M Baker
E Smyth
M Miller
P Parker

Thanks!

Also posted here:

http://www.mrexcel.com/forum/subscription.php?folderid=0

http://www.excelforum.com/exce…matting-names-nicely.html

**royUK** · Oct 15th 2015

Re: Clever way of formatting names nicely?

Why would you need VBA? Formulas are the obvious and best way.

jindon · Oct 15th 2015

Re: Clever way of formatting names nicely?

Code

Sub test()
    Dim r As Range, temp As String, gName As String, fName As String
    With CreateObject("VBScript.RegExp")
        .Pattern = "([^ \.,]+)([ ,\.]) *([^ \.\,]+).*"
        For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
            If .test(r.Value) Then
                temp = .Replace(r.Value, "$2")
                If temp = "," Then
                    gName = Left$(UCase$(.Replace(r.Value, "$3")), 1)
                    fName = " $1"
                Else
                    gName = Left$(UCase$(.Replace(r.Value, "$1")), 1)
                    fName = " $3"
                End If
                r(, 2).Value = StrConv(.Replace(r.Value, gName & fName), 3)
            Else
                r(, 2).Value = StrConv(r.Value, 3)
            End If
        Next
    End With
End Sub

Display More

Clever way of formatting names nicely?

Participate now!

Share