How to extract first letter of each word from cell?
Imagine you have a list of country names in your worksheet and you want to extract the first letter of each word in these names. Excel does not provide a direct feature for extracting the initial letters of each word in a cell. However, I will introduce some practical methods to efficiently accomplish this task, making the process straightforward and effective.
Extract first letter of each word from Cell with formula
To get the first letter of each word within a cell, the following array formula can help you.
1. Please copy the following formula into a blank cell where you want to get the result, and then press Ctrl + Shift + Enter keys together to get the first result.
=CONCAT(LEFT(FILTERXML("<a><b>"&SUBSTITUTE(A2," ","</b><b>")&"</b></a>","//b"),1))
2. Next, pull the formula down to fill the other cells. This action will automatically extract the first letter of each word in each cell all at once. See screenshot:
- SUBSTITUTE(A2," ","</b><b>"):This function replaces each space in the text in cell A2 with . This effectively separates each word in the text with these XML tags. For example, if A2 contains "Hello World", this part of the formula turns it into "Hello</b><b>World".
- "<a><b>"&...&" </b></a>": This part wraps the result of the SUBSTITUTE function with <a><b> at the beginning and </b></a> at the end. Continuing with the example, the string now becomes <a><b>Hello</b><b>World</b></a>, forming a valid XML structure where each word is enclosed within <b> tags.
- FILTERXML(...,"//b"): FILTERXML is used to parse the XML string created in the previous steps. The XPath query //b selects all elements within the <b> tags, i.e., each word in the original string. Using our example, FILTERXML would return an array with two elements: "Hello" and "World".
- LEFT(...,1): The LEFT function is then applied to each element of the array returned by FILTERXML, extracting the first letter of each word. In the example, this would result in "H" and "W".
- CONCAT(...): Finally, the CONCAT function concatenates all elements of the array into a single string. For our "Hello World" example, it would concatenate "H" and "W" to produce "HW".
Extract first letter of each word from Cell with Kutools AI Aide
Quickly extract the first letter of each word in a cell with Kutools AI Aide. No need for complex formulas; the AI assistant automates the task for you, making data processing simple and efficient. Streamline your Excel workflow and make your work easier. Try Kutools AI Aide and experience smart Excel operations!
After installing Kutools for Excel, please click Kutools AI > AI Aide to open the Kutools AI Aide pane:
- Select the data list, then type your requirement into the chat box, and click Send button or press Enter key to send the question;
- After analyzing, click Execute button to run. Kutools AI Aide will process your request using AI and return the results directly in Excel.
Extract first letter of each word from Cell with User Defined Function
Extracting the first letter of each word from a cell is a task that can be greatly optimized by using a User Defined Function (UDF) in Excel. This section explores how to create and use a UDF to efficiently perform this task.
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
Function GetFirstLetters(rng As Range) As String
'Updateby Extendoffice
Dim arr
Dim I As Long
arr = VBA.Split(rng, " ")
If IsArray(arr) Then
For I = LBound(arr) To UBound(arr)
GetFirstLetters = GetFirstLetters & Left(arr(I), 1)
Next I
Else
GetFirstLetters = Left(arr, 1)
End If
End Function
3. Then save and close this code, go back the worksheet, and enter this formula =GetFirstLetters(A2) into a blank cell. And then, drag the fill handle to the cells that you want to apply this formula. And all the first letters have been extracted from the series of words, see screenshot:
Related articles:
- Extract first or last two or n words from text string
- If you have a list of text strings which are separated by space, and now, you want to extract first or last three or n words from the cell value to get the following screenshot result. This article, I will introduce some formulas to extract first or last two or n words from text string in Excel.
- Extract text before/after space or comma only
- When you want to extract the text before or after the space from the list as shown as below, do you have a good way to get it done? Let me tell you some trick ways to extract text before or after space only in Excel.
- Extract email address from text string
- When you import some email addresses form Website to Excel worksheet, there always contains irrelevant text, but now you just want to extract the pure email addresses from the text string (see following screenshots). How could you quickly only get the email addresses from the cell text?
- Extract string between two different characters
- If you have a list of string in Excel which you need to extract part of string between two characters from as below screenshot shown, how to handle it as quickly as possible? Here, I introduce some methods about solving this job.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!