Create Initials list from a list of names

nightracer

Board Regular
Joined
May 17, 2004
Messages
147
Office Version
  1. 365
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.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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.
 
Upvote 0
Thank you Eric, appreciate the quick reply.
I am hoping to automate it rather than have a manual step, and I do have CONCAT?
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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 ;)
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top