Is there a way to swap around multiple names in one Excell cell?

[Assie281]

Hi there, is there a way (either formula or VB) to swap multiple names in one Excel cell?

Example of current data in one cell:
Victoria Wood, David Jones, Sharon Smith, Caroline S Petit, Martine P Smar, Elizabeth A Gilley, Alex Davies, Andrew P Collins

The format I want to convert it to (in one cell):
Wood V, Jones D, Smith S, Petit C, Smar M, Gilley E, Davis A, Collins P

Many thanks for looking into this,

Astrid

 

[jmacleary]

Hi there. Assuming your data is as per your example (i.e. the surname is always immediately before the comma or on the end) and the desired initial is the first character of each name, this will do it:

Function SwapNames(thiscell As Range)
namelist = Split(thiscell.Value, ",")
For Each thisname In namelist
    lastSpace = InStrRev(thisname, " ")
    surname = Mid(thisname, lastSpace + 1)
    SwapNames = SwapNames +" "+ surname + " " + Left(LTrim(thisname), 1) + ","
Next
SwapNames = Left(SwapNames, Len(SwapNames) - 1)

End Function

Inpu it as a function in a module of your workbook, then use =SwapNames(A2) to get cell A2 reformatted. You will get a #value error if you enter a reference to more than 1 cell, or to an empty cell.

 

[aneee]

Thank you for your answer John! I was looking for such solution for long.



popup maker

 

[MickG]

Too Quick !!

 

[Rick Rothstein]

Here is a slightly different way to write the SwapNames function...

Function SwapNames(S As String) As String
  Dim X As Long, Nm As Variant
  Nm = Split(S, ",")
  For X = 0 To UBound(Nm)
    Nm(X) = Trim(Nm(X))
    Nm(X) = Mid(Nm(X), InStrRev(Nm(X), " ") + 1) & " " & Left(Nm(X), 1)
  Next
  SwapNames = Join(Nm, ", ")
End Function