• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract first letter from each word in cell

trprasad78

trprasad78

Member
I have list of company name I like to create short code from that.

Example
Hindustan Private Limited
Chandoo organisation
Google Inc
Witco India Pvt Ltd

Expected out put
HPL
CO
GI
WIPL

Please suggest the code to extract letters.

thank you in advance.
 
If you have access to Concat function... you can do something like below as well.
Assuming string in A1 to A4.
=CONCAT(LEFT(FILTERXML("<a><b>"&SUBSTITUTE(A1," ","</b><b>")&"</b></a>","//b"),1))

Copy down. Depending on version you may need to confirm as array (CTRL + SHIFT + ENTER).
 
FilterXML is mostly used to parse and retrieve info from XML (eXtensible Markup Language) data. XML if often used to transport data across networks.

Using substitute function on a string, you construct base XML. <a><b>Word1</b><b>Word2</b></a>.

Then FilterXML is used to extract specific node from the string. "//b" extracts all <b> nodes as array. Using Left(,1) to extract 1st character of each node. Concat is used to concatenate array of 1st letters.
 
In Excel, you can create a User Defined Function to extract the first letters of every words in a cell. Please do as this:
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.< /p>

>>> use code - tags <<<
Code: 
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

3. 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)

https://www.extendoffice.com/documents/excel/1656-excel-extract-first-character.html
 
Last edited by a moderator:
Chihiro said:
FilterXML is mostly used to parse and retrieve info from XML (eXtensible Markup Language) data. XML if often used to transport data across networks.

Using substitute function on a string, you construct base XML. <a><b>Word1</b><b>Word2</b></a>.

Then FilterXML is used to extract specific node from the string. "//b" extracts all <b> nodes as array. Using Left(,1) to extract 1st character of each node. Concat is used to concatenate array of 1st letters.
Click to expand...
hi @Chihiro ,

Could you Please share useful like about filterxml function.
 
You must log in or register to reply here.
Back
Top