Create Initials list from a list of names

nightracer

Board Regular
Joined
May 17, 2004
Messages
137
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.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,808
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

Board Regular
Joined
May 17, 2004
Messages
137
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,808
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:

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

Board Regular
Joined
May 17, 2004
Messages
137
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,808
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,474
with PowerQuery (Get&Transform)

Column1Initials
Paul JonesPJ
Adam SimpsonAS
Jan Van de ValtJVdV
Tony TileTT

Code:
[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 ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,100,139
Messages
5,472,747
Members
406,835
Latest member
steve43040

This Week's Hot Topics

Top