Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Extract initials from names in Excel
In this tutorial, we will be going over how to extract initials from cells in Microsoft Excel. This will involve use of combined functions in excel, and since there are slightly different ways to do this we will be going over both.
Using Left, Find, and Mid Functions Together with &
To understand this method we should go over how the 3 key functions work −
The left function in excel will return the first characters (or characters if an amount is specified) of a cell.
The find function will give the position index of a specified character in a cell.
The mid function will return characters from the middle part of a string where we can specify where to start and how many to return.
With a bit of intuition we can use these three functions together with the help of &. The initials will consist of the first character of the first name and the first character of the second name.
To get the former we use the left function, and the latter we combine the mid and find function. We implement the find function within the mid function to address where we start from, which will be the first beginning of the last name which comes one index after the blank space.
In the picture below we have our name column set up and an empty initials column to be filled through our formula written.
The following result is achieved −
Using Left, Find, and Mid Functions together with Concatenate Function
Similarly, we can use the concat function in excel to achieve the same goal. Remember that to concatenate something means to join together. In this case the first argument is the first character of the first name retrieved through the left function and the second character is the first character of the last name retrieved from the combination of the mid and find function.
We notice the output here is just for one row, so we simply click on the GG initial cell and drag the handler to the bottom to have the same formula applied to all cells.
2K+ Views
