'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Extract the First Letter of Each Word in Excel Formulas
Question details
The user needs to extract the first letter from each word within a single Excel cell, with an option to place a custom separator between the extracted characters.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Text manipulation in spreadsheets to generate initials or acronyms.
- Observed behavior
- Requires a formula to isolate and combine the starting letters of multiple words in a cell.
Determine the maximum number of words your cells contain, as simple formulas work best for exactly two words, while dynamic array formulas are better for varying word counts. Ensure your text has no irregular double spaces.
Use the LEFT and MID Functions with Ampersand
This is the most direct method to extract first letters for cells containing exactly two words.
By combining the LEFT function to get the first letter of the first word, and the MID function to get the first letter after the space, you can quickly generate two-letter initials.
Click on an empty cell next to your target text (for example, click B1 if your text is in A1).
Type the formula =LEFT(A1,1)&"_"&MID(A1,FIND(" ",A1)+1,1) into the formula bar.
Press Enter to see the result. Drag the fill handle down from the bottom-right corner of the cell to apply the formula to the rest of your column.
Use the CONCATENATE Function
An alternative approach for users who prefer using the CONCATENATE function instead of the ampersand symbol for joining text.
Use TEXTSPLIT for Variable Word Counts
If your cells contain more than two words, newer dynamic functions like TEXTSPLIT and TEXTJOIN are much more efficient.
Perform Complex Formula Calculations in WPS Spreadsheet
WPS Spreadsheet provides full compatibility with standard Excel text formulas like LEFT, MID, FIND, and modern dynamic arrays, allowing you to manipulate strings efficiently.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the text data.
- 2. Enter your formula: Select a blank cell and input your LEFT and MID formula to extract initials.
- 3. Drag to fill: Use the smart fill handle to copy the extraction formula across all your rows instantly.
Frequently Asked Questions
How do I extract initials without any separator at all?
You can remove the separator from the formula by deleting the underscore and the quotes. For example, use =LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1) to combine the letters directly.
What happens if there are extra spaces in my text?
Extra spaces will cause the FIND function to return incorrect results, as it looks for the very first space. Wrap your cell reference in the TRIM function first, like TRIM(A1), to remove any irregular spacing before extracting letters.
Can I extract the first three letters of each word instead of just one?
Yes. Change the number of characters in the LEFT and MID functions from 1 to 3. For example, use LEFT(A1,3) and MID(A1,FIND(" ",A1)+1,3).
