Table of Contents
To extract the first letter of each word in Excel, you can use the LEFT function. This function allows you to specify the number of characters to extract from a given text. By setting the number to 1, you can extract the first letter of each word in a cell or range of cells. Alternatively, you can also use the MID function to achieve the same result by specifying the starting position as 1 and the number of characters as 1. This method can be useful for organizing and manipulating data in Excel, especially for tasks such as creating acronyms or abbreviations.
Excel: Extract First Letter of Each Word
You can use the following formula in Excel to extract the first letter of each word in a cell:
=CONCAT(LEFT(FILTERXML("<a><b>"&SUBSTITUTE(A2," ","</b><b>")&"</b></a>","//b"),1))
This particular formula extracts the first letter of each word in cell A2.
For example, if cell A2 contains The Dallas Mavericks then this formula would return TDM.
The following example shows how to use this formula in practice.
Example: Extract First Letter of Each Word in Excel
Suppose we have the following column of strings in Excel:
Suppose we would like to extract the first letter from each word in the cells in column A.
We can type the following formula into cell B2 to do so:
=CONCAT(LEFT(FILTERXML("<a><b>"&SUBSTITUTE(A2," ","</b><b>")&"</b></a>","//b"),1))
We can then click and drag this formula down to each remaining cell in column B:
Column B now contains the first letter from each word in column A.
For example:
- The Dallas Mavericks returns TDM.
- Mighty Ducks returns MD.
- San Antonio Spurs returns SAS.
And so on.
How This Formula Works
Recall the formula that we used to extract the first letter from each word in cell A2:
=CONCAT(LEFT(FILTERXML("<a><b>"&SUBSTITUTE(A2," ","</b><b>")&"</b></a>","//b"),1))
This formula uses the FILTERXML function and the SUBSTITUTE function to first convert the string The Dallas Mavericks into <a><b>The</b><b>Dallas</b><b>Mavericks</b></a>
Then we use //b to extract all values in between each <b> and </b> and then we use the LEFT function to extract only the first character from each word.
Lastly, we use the CONCAT function to concatenate together each of these first letters into the final acronym.
The end result is that the formula is able to return TDM from The Dallas Mavericks.
The same process is repeated for each string.
Note: You can find the complete documentation for the FILTERXML function in Excel .
Additional Resources
The following tutorials explain how to perform other common operations in Excel: