Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to extract first letter of each word from cell?
Excel is a powerful tool that offers numerous functions to manipulate and extract data. Extracting the first letter of each word from a cell can be particularly useful when you need to analyse or categorize text data. In this article, we will explore a step−by−step guide on how to extract the first letter of each word from a cell in Excel.
Step 1
Open Excel and Enter Data Start by opening Microsoft Excel and entering your desired text into a cell. For the purpose of this tutorial, let's assume our text is in Cell A2.
Step 2
Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
Step 3
Click Insert > Module, and paste the following code in the Module Window.
Function GetFirstLetters(rng As Range) As String
'Update 20140325
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
Step 4
Then save and close this code, go back the worksheet, and enter this formula =GetFirstLetters(A2) (A2 indicates the cell which you want to extract the first letter, you can change it as you need) into a blank cell. See screenshot −
Step 5
And then press Enter key, and select cell B2, 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 −
Conclusion
By following these steps, you can easily extract the first letter of each word from a cell in Excel. This technique is helpful for various data manipulation tasks, such as analysing names, creating initials, or generating abbreviations. Excel's flexibility and functions make it a valuable tool for working with text data efficiently.
2K+ Views
