Change "Last name, first initial" to first name last name in sentences

kmprice710

Board Regular
Joined
Jan 8, 2014
Messages
87
Office Version
  1. 2019
Platform
  1. Windows
I have Office Professional Plus 2019.

How do I fix this?

I have a column of cells with phrases like

"Adams, a went to the bathroom"
"Smith, d. lifted weights"
"Jones, maxwell ate dinner"
"The store would not serve Williams, t.j."
The shoe did not fit Thompson, f."
"Stuart, d and Pyle, Gerhard did not like Velma, uly"

The info is presented as "Last name, first name" or "Last name, first initial" or "Last name, first initial with a period" or "Last Name, first two initials" or "Last Name, first two initials with periods" with the first name or first initial in lower case.
The name can appear at the beginning, in the middle or at the end of the cell.

I want to change the cells to:

"A Adams went to the bathroom"
"D. Smith lifted weights"
"Maxwell Jones ate dinner"
"The store would not serve T.J. Williams"
"The shoe did not fit F. Thompson"
"D Stuart and Gerhard Pyle did not like Uly Velma"

I don't need end-of-sentence punctuation.

How do I do it?
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
One way is to use VBA.

Book1
AB
1TextProcessed Text
2Adams, a went to the bathroomA Adams went to the bathroom
3Smith, d. lifted weightsD. Smith lifted weights
4Jones, maxwell ate dinnerMaxwell Jones ate dinner
5The store would not serve Williams, t.j.The store would not serve T.J. Williams
6The shoe did not fit Thompson, f.The shoe did not fit F. Thompson
7Stuart, d and Pyle, Gerhard did not like Velma, ulyD Stuart and Gerhard Pyle did not like Uly Velma
Sheet1


VBA Code:
Sub DoSomething()
    Dim WS As Worksheet
    Dim rngColData As Range
    Dim R As Range
    Dim I As Long
    Dim S As String, Txt As String
    Dim SA As Variant

    Set WS = ActiveSheet

    With WS
        Set rngColData = .Range("A2", .Range("A" & .Rows.Count).End(xlUp))    'range to last cell in column w/data
    End With

    For Each R In rngColData
        S = Replace(R.Value, ", ", ",")
        SA = Split(S, " ")
        Txt = ""
        For I = 0 To UBound(SA)
            If InStr(SA(I), ",") > 0 Then
                Txt = Txt & Application.Proper(Split(SA(I), ",")(1)) & " " & Split(SA(I), ",")(0) & " "
            Else
                Txt = Txt & SA(I) & " "
            End If
        Next I
        R.Offset(0, 1).Value = Trim(Txt)
    Next R
End Sub
 
Upvote 0
I have Office Professional Plus 2019.
In that case do you need to amend your account details?
1687682390520.png


Another option is you could test this user-defined function.

VBA Code:
Function FixNames(S As String) As String
  Dim RX As Object, M As Object
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "(\b[A-Z][^ \,]+)(\, *)([^ ]+)"
  For Each M In RX.Execute(S)
    S = Replace(S, M, Application.Proper(M))
  Next M
  FixNames = RX.Replace(S, "$3 $1")
End Function

kmprice710.xlsm
AB
1Adams, a went to the bathroomA Adams went to the bathroom
2Smith, d. lifted weightsD. Smith lifted weights
3Jones, maxwell ate dinnerMaxwell Jones ate dinner
4The store would not serve Williams, t.j.The store would not serve T.J. Williams
5The shoe did not fit Thompson, f.The shoe did not fit F. Thompson
6Stuart, d and Pyle, Gerhard did not like Velma, ulyD Stuart and Gerhard Pyle did not like Uly Velma
7 
8No names hereNo names here
Sheet1
Cell Formulas
RangeFormula
B1:B8B1=FixNames(A1)
 
Upvote 0
Solution

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top