Extract first initial and last name from full name
Supposing, you have a list of full names, now, you would like to extract the first initial and last name or first name and first character of last name from the full name as below screenshots shown. How could you deal with these tasks in Excel workbook?
 |
 |
- Extract first initial and last name from full name in Excel
- Extract first name and first character of last name from full name in Excel
Extract first initial and last name from full name in Excel
To extract the first initial and last name, the combination of LEFT, RIGHT, LEN and FIND functions can do you a favor, the generic syntax is:
- text: A full name or cell value that you want to use.
Please enter or copy the following formula into a blank cell:
Then, drag the fill handle down to the cells that you want to apply this formula, and you will get all first initial and last names have been extracted, see screenshot:
Explanation of the formula:
1. LEFT(A2,1): This LEFT function is used to extract the first character of the cell A2. This will get the first initial “J”.
2. RIGHT(A2,LEN(A2)-FIND(" ",A2)+1):
- FIND(" ",A2): This FIND function returns the position of the space character. It will get the number 6.
- LEN(A2)-FIND(" ",A2)+1: The length of A2 subtracts the number of the position space character, adding 1 means to include the space character when extracting the data. This will number of characters to be returned from the end of the cellA2. This is recognized as the num_chars of the RIGHT function.
3. LEFT(A2,1)&RIGHT(A2,LEN(A2)-FIND(" ",A2)+1): At last, using the & symbol to join the first initial returned by the LEFT function and last name which returned by the RIGHT function.
Extract first name and first character of last name from full name in Excel
If you need to extract the first name and the first character of last name, the LEFT and FIND functions can help you. The generic syntax is:
- text: A full name or cell value that you want to use.
Please copy or enter the below formula into a blank cell:
And then, drag the fill handle down to the cells that you want to apply this formula, and all the first names and the first character of the last names have been extracted, see screenshot:
Explanation of the formula:
FIND(" ",A2)+1: The FIND function is used to return the position of the space character in cell A2, adding 1 means to get the position of the first character after the space. This will be recognized as the num_chars argument in the LEFT function.
LEFT(A2,FIND(" ",A2)+1): This LEFT function is used to extract the number of characters that returned by the FIND function from the left side of cell A2.
Relative functions used:
- LEFT:
- The LEFT function extracts the given number of characters from the left side of a supplied string.
- RIGHT:
- The RIGHT function is used to extract a specific number of characters from the right side of the text string
- LEN:
- The LEN function returns the number of characters in a text string.
- FIND:
- The FIND function is used to find a string within another string, and returns the starting position of the string inside another one
More articles:
- Flip Or Reverse First And Last Names In Excel List
- If you have a list of full names which are formatted as last name and first name, now, you want to flip the last and first names to first and last as below screenshot shown. This article, I will introduce some formulas for dealing with this job in Excel.
- Extract Multiple Lines From A Cell
- If you have a list of text strings which are separated by line breaks (that occurs by pressing Alt + Enter keys when entering the text), and now, you want to extract these lines of text into multiple cells as below screenshot shown. How could you solve it with a formula in Excel?
- Extract Nth Word From Text String In Excel
- If you have a list of text strings or sentences, now, you want to extract the specific nth word from the list as below screenshot shown. This article, I will introduce some methods for solving this job in Excel.
- Extract Text Between Parentheses From Text String
- If there is part of the text surrounded with the parentheses within the text string, now, you need to extract all the text strings between the parentheses as following screenshot shown. How could you solve this task in Excel quickly and easily?
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.
- Designed for 1500 work scenarios, helps you solve 80% Excel problems.
- Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
- Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
- 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New Internet Explorer.
