Learn how to get initials in Excel spreadsheets with a list of full names. If you have your contacts imported into Excel, or just keep a list of names for employees or other reasons, there is a formula that you can enter to retrieve the initials of each contact to make search functions easier within the spreadsheet. This can be helpful when organizing large amounts of data, listing people alphabetically, or simply finding people faster in a contact list.
Once you have the spreadsheet downloaded, you will see a list of contacts with full names that can be used to get initials from. The first contact, in cell B6, has had their information changed into initials in cell D6. Your goal in this example is to retrieve the information in cell B7, Jeremy Adam Anderson, and put the initials in cell D7.
To do this, select cell D7 and enter the following formula:
=IF(LEN(B7)-LEN(SUBSTITUTE(B7,” “,””))=0,LEFT(B7,1),IF(LEN(B7)-LEN(SUBSTITUTE(B7,” “,””))=1,LEFT(B7,1)&MID(B7,FIND(” “,B7)+1,1),LEFT(B7,1)&MID(B7,FIND(” “,B7)+1,1)&MID(B7,FIND(” “,B7,FIND(” “,B7)+1)+1,1)))
After this is entered, D7 should read “JAA”.
To apply this formula to the rest of the contacts, take the corner of cell D7 and drag it down the column to D14. This will give every contact their initials in the D column.
When working in your own contacts list, take the above formula and change all the instances of “B7” to the cell you want to retrieve information from to make initials. You can then drag that cell where you want the formula applied and turn all of your contacts into initials.