VBA/Formula - Get Initials from Full Name - Multiple names in cell

[The Great SrH]

Hi guys,

I can't find a code that will help me with this anywhere. I have a cell in Excel that may contain multiple names separated by a comma - I want to have these names converted to Initials but still separated by commas eg:

Cell A2 contains "John Smith, Betty White, Michael Jackson".

I want it to display "JS, BW, MJ" instead.

Happy for it to be VBA or a formula!

Thanks!

 

[steve the fish]

Heres a function:

Function INIT(rng As Range) As String

arr = Split(rng, ",")
For i = LBound(arr) To UBound(arr)
    arr(i) = Trim(arr(i))
    If InStr(arr(i), " ") > 0 Then
        INIT = INIT & Left(arr(i), 1) & Mid(arr(i), InStr(arr(i), " ") + 1, 1) & ","
    End If
Next
INIT = Left(INIT, Len(INIT) - 1)

End Function

 

[The Great SrH]

Thanks Steve - Unfortunately this only keeps the first name in the cell?

 

[steve the fish]

Whats in the cell?

 

[István Hirsch]

Try this:

Function GetCap(s As String) As String
  s = Replace(s, " ", "")
    For i = 1 To Len(s)
    If Mid(s, i, 1) Like "[!a-z]" Then GetCap = GetCap & Mid(s, i, 1)
    Next
  GetCap = Replace(GetCap, ",", ", ")
End Function

 

[The Great SrH]

Whats in the cell?

Just one persons initials

Try this:

Function GetCap(s As String) As String
  s = Replace(s, " ", "")
    For i = 1 To Len(s)
    If Mid(s, i, 1) Like "[!a-z]" Then GetCap = GetCap & Mid(s, i, 1)
    Next
  GetCap = Replace(GetCap, ",", ", ")
End Function

Works perfect! Thank you

 

[István Hirsch]

You are welcome.