Follow our guide to learn how to get initials from names in Excel spreadsheets with full names. When you have a long list of contacts in an Excel worksheet, there is a formula you can input that will automatically take the names entered and get their initials.
Download the example spreadsheet to follow along and learn how to get initials.
Open the downloaded sheet and you’ll see a list of contact names and phone numbers. In cell D6, the name Melissa Gardner has already been abbreviated to MG. For the example, your goal is fill cell D7 with the initials from Jeremy Anderson.
Enter this formula into cell D7:
=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)))
D7 should then be populated with “JAA”.
Instead of entering the formula for each individual name, you can drag down cell D7 and it will fill in the formula for the rest of the cells.
When using this formula in your own contact spreadsheet, all you need to change in the formula is the “B7” parts to whatever cell you are referencing to retrieve initials from. Then you can drag the abbreviated cell down the column and it will fill in the rest of the contacts’ initials for you.
Related Templates:
- Remove Middle Initial in Excel
- Get First Name from Full Name
- Remove Spaces Between Words in Excel
- Count Cells in Excel Formula
- Excel Left Function
View this offer while you wait!