Table of Contents
Extracting the first initial and last name from an Excel spreadsheet can be done in a few simple steps. Firstly, identify the column that contains the full names and insert two empty columns next to it. Then, in the first empty column, use the LEFT function to extract the first initial from the full name. In the second empty column, use the RIGHT and FIND functions to extract the last name from the full name. Finally, you can use the CONCATENATE function to combine the first initial and last name into one cell. This process can be repeated for the entire column to extract the first initial and last name from each name in the spreadsheet.
Excel: Extract First Initial and Last Name
You can use the following formula in Excel to extract the first initial and last name from a full name in a cell:
=LEFT(A2,1)&" "&(TEXTAFTER(A2," "))
This particular example extracts the first initial and last name from the full name in cell A2.
For example, if cell A2 contained the full name John Smith then this formula would return J Smith.
The following example shows how to use this formula in practice.
Example: Extract First Initial and Last Name in Excel
Suppose we have the following list of full names in Excel:
Suppose we would like to extract the first initial and last name from each full name.
We can type the following formula into cell B2 to do so:
=LEFT(A2,1)&" "&(TEXTAFTER(A2," "))We can then click and drag this formula down to each remaining cell in column B:
Column B now contains the first initial and last name of each full name in column A.
How This Formula Works
Recall the formula that we used to extract the first initial and last name from the full name (John Smith) in cell A2:
=LEFT(A2,1)&" "&(TEXTAFTER(A2," "))First, we used LEFT(A2,1) to extract the first character from the cell.
This extracted J.
Then we used &” “& to concatenate a single space.
Then we used TEXTAFTER(A2, ” “) to extract all text after the space in the cell.
This extracted Smith.
The end result is that the formula returned J Smith.
The same logic was used to extract the first initial and last name from each full name in column A.
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I extract the first initial and last name from an Excel spreadsheet?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-extract-the-first-initial-and-last-name-from-an-excel-spreadsheet/
stats writer. "How can I extract the first initial and last name from an Excel spreadsheet?." PSYCHOLOGICAL SCALES, 22 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-extract-the-first-initial-and-last-name-from-an-excel-spreadsheet/.
stats writer. "How can I extract the first initial and last name from an Excel spreadsheet?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-extract-the-first-initial-and-last-name-from-an-excel-spreadsheet/.
stats writer (2024) 'How can I extract the first initial and last name from an Excel spreadsheet?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-extract-the-first-initial-and-last-name-from-an-excel-spreadsheet/.
[1] stats writer, "How can I extract the first initial and last name from an Excel spreadsheet?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I extract the first initial and last name from an Excel spreadsheet?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.