Create Initials list from a list of names

nightracer

Board Regular
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 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.

Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.

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

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)

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?

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.

with PowerQuery (Get&Transform)

 Column1 Initials Paul Jones PJ Adam Simpson AS Jan Van de Valt JVdV Tony Tile TT

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

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

Replies
1
Views
797
Replies
7
Views
2K
Replies
6
Views
572
Replies
0
Views
473
Replies
10
Views
1K

1,206,814
Messages
6,075,009
Members
446,114
Latest member

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.

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

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