Create Initials list from a list of names

[nightracer]

Hi

Is there a way to either extract just the Capitals from a name list, or use the space to extract the first letter of each name?

In cell A1 Paul Jones
In cell A2 Adam Simpson
In cell A3 Jan Van de Valt
In cell A4 Tony Tile

Can I automate:
C1 = PJ
C2 = AS
C3 = JVV (or JVdV)
C4 = TT

Any help appreciated.

 

[Eric W]

One quick way is to use Flash fill. Put PJ in B1. With the selection still in column B, press Control-E, and Excel will fill in the rest of the column. You may have to move it to column C at that point though.

If you have CONCAT (one of the newer Excel functions), we can create a formula to create this for you. If you don't have CONCAT, we can write a custom function for you. Let us know.

 

[nightracer]

Thank you Eric, appreciate the quick reply.
I am hoping to automate it rather than have a manual step, and I do have CONCAT?

 

[Eric W]

CONCAT is available in Excel 365. If you have that, you can try this formula:

=CONCAT(IF(MID(" "&A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ",MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),""))

and confirm it with Control+Shift+Enter, not just enter.


Without CONCAT, you can use a UDF (User-Defined function). Open a copy of your workbook. Press Alt-F11 to open the VBA editor. From the menu, select Insert > Module. On the sheet that opens, paste this code:

Public Function GetInits(ByVal str1 As String)
Dim i As Long
    Application.Volatile
    str1 = " " & str1
    For i = 1 To Len(str1) - 1
        If Mid(str1, i, 1) = " " Then GetInits = GetInits & Mid(str1, i + 1, 1)
    Next i
End Function

Press Alt-Q to close the editor. Now just use this formula:

=Getinits(A1)

 

[nightracer]

Thanks Eric, the CONCAT formula returns the first letter of the first word, which I guess I could just do with a LEFT formula.
Do I have something wrong to return subsequent capitals?

Again, appreciate your help.

 

[Eric W]

In order for it to return all the capitals, you need to identify it as an array formula. Select the cell with the formula, press F2 to start editing it, then hold down the Shift and Control keys, then press Enter. That should get all of the capitals.

If that still doesn't return all of them, then you might have a different character code than the usual space. We can check that by looking at one of your cells. If you have a name in A1, and the 5th position is a space, you can enter this formula:

=CODE(MID(A1,5,1))

It should be 32, but if it's not, we can adjust the main formula.

 

[sandy666]

with PowerQuery (Get&Transform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td][/td][td=bgcolor:#70AD47]Initials[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Paul Jones[/td][td][/td][td=bgcolor:#E2EFDA]PJ[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Adam Simpson[/td][td][/td][td]AS[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Jan Van de Valt[/td][td][/td][td=bgcolor:#E2EFDA]JVdV[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Tony Tile[/td][td][/td][td]TT[/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
    #"Extracted First Characters" = Table.TransformColumns(#"Split Column by Delimiter", {{"Column1.1", each Text.Start(_, 1), type text}, {"Column1.2", each Text.Start(_, 1), type text}, {"Column1.3", each Text.Start(_, 1), type text}, {"Column1.4", each Text.Start(_, 1), type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Extracted First Characters",{"Column1.1", "Column1.2", "Column1.3", "Column1.4"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Initials")
in
    #"Merged Columns"[/SIZE]

only few clicks

 

[nightracer]

Thanks for the explanation Eric, that is now working.
Much appreciated.

 

[Eric W]

Glad to help.