Table of Contents
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 .