Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: get the first letter of each word in a string
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2018
    Posts
    267
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default get the first letter of each word in a string

    say, I have a string: "The Greatest Show On Earth"
    I just want to have the first letters of each word: "TGSOE"
    pls help with the formula (not VBA) - maximum words per string = 5
    many many thanks

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,805
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: get the first letter of each word in a string

    I don't think you will do it with formula.....unless maybe the string is always going to be the same length.
    Probably the best option would be a UDF, like this ffrom ExtendOffice

    Code:
    Function GFL(rng As Range) As String
        Dim arr
        Dim I As Long
        arr = VBA.Split(rng, " ")
        If IsArray(arr) Then
            For I = LBound(arr) To UBound(arr)
                GFL = GFL & Left(arr(I), 1)
            Next I
        Else
            GFL = Left(arr, 1)
        End If
    End Function
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: get the first letter of each word in a string

    Try one of these, copied down.
    B1 requires the CONCAT function which is only available in later versions of Excel and is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.
    The C1 formula should work in all versions and does not require the Ctrl+Shift+Enter confirmation.

    First letters

    ABC
    1The Greatest Show On EarthTGSOETGSOE
    2
    3Today is FridayTiFTiF

    Spreadsheet Formulas
    CellFormula
    B1{=CONCAT(IF(MID(" "&A1,ROW(INDIRECT("1:" & LEN(A1)+1)),1)=" ",MID(" "&A1,ROW(INDIRECT("2:" & LEN(A1)+1)),1),""))}
    C1=LEFT(A1,1)&MID(A1,FIND("#",SUBSTITUTE(A1&REPT(" ",4)," ","#",1))+1,1)&MID(A1,FIND("#",SUBSTITUTE(A1&REPT(" ",4)," ","#",2))+1,1)&MID(A1,FIND("#",SUBSTITUTE(A1&REPT(" ",4)," ","#",3))+1,1)&MID(A1,FIND("#",SUBSTITUTE(A1&REPT(" ",4)," ","#",4))+1,1)
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  4. #4
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: get the first letter of each word in a string

    Hi,

    Here's a formula solution, a little lengthy thou:

    AB
    1The Greatest Show On EarthTGSOE
    2Just Another SampleJAS

    Sheet255



    Worksheet Formulas
    CellFormula
    B1=LEFT(A1,1)&LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),100,100)),1)&LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),200,100)),1)&LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),300,100)),1)&LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),400,100)),1)

    Last edited by jtakw; Sep 13th, 2018 at 08:02 PM.

  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: get the first letter of each word in a string

    Quote Originally Posted by jtakw View Post
    Here's a formula solution, a little lengthy thou:
    jtakw's suggestion triggered another idea for the CONCAT function (in D1) that
    - is shorter than my earlier CONCAT formula
    - avoids the volatile INDIRECT function
    - does not require the Ctrl+Shift+Enter confirmation

    First letters

    ABCD
    1The Greatest Show On EarthTGSOETGSOETGSOE
    2
    3Today is FridayTiFTiFTiF

    Spreadsheet Formulas
    CellFormula
    B1{=CONCAT(IF(MID(" "&A1,ROW(INDIRECT("1:" & LEN(A1)+1)),1)=" ",MID(" "&A1,ROW(INDIRECT("2:" & LEN(A1)+1)),1),""))}
    C1=LEFT(A1,1)&MID(A1,FIND("#",SUBSTITUTE(A1&REPT(" ",4)," ","#",1))+1,1)&MID(A1,FIND("#",SUBSTITUTE(A1&REPT(" ",4)," ","#",2))+1,1)&MID(A1,FIND("#",SUBSTITUTE(A1&REPT(" ",4)," ","#",3))+1,1)&MID(A1,FIND("#",SUBSTITUTE(A1&REPT(" ",4)," ","#",4))+1,1)
    D1=CONCAT(LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),{0,1,2,3,4}*100+1,100)),1))
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  6. #6
    Board Regular
    Join Date
    Mar 2018
    Posts
    267
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: get the first letter of each word in a string

    Mr. Michael M
    thank you for your reply..

  7. #7
    Board Regular
    Join Date
    Mar 2018
    Posts
    267
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: get the first letter of each word in a string

    Mr. jtakw
    thank you for your assistance..

  8. #8
    Board Regular
    Join Date
    Mar 2018
    Posts
    267
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: get the first letter of each word in a string

    Mr. Peter_SSs
    thank you for your kind assistance.
    the other formula - I don't have any problem.
    with: (I prefer this one because of brevity)
    =CONCAT(LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),{0,1,2,3,4}*100+1,100)),1))

    I am getting #NAME ?
    what could be the problem?
    many thanks
    Last edited by LFKim2018; Sep 13th, 2018 at 08:27 PM.

  9. #9
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: get the first letter of each word in a string

    Mr. jtakw
    thank you for your assistance..
    You're welcome.

    Mr. Peter_SSs
    I am getting #NAME ?
    what could be the problem?
    That would mean you Don't have the CONCAT function, it's only available with an Office 365 subscription.
    Last edited by jtakw; Sep 13th, 2018 at 08:34 PM.

  10. #10
    Board Regular
    Join Date
    Mar 2018
    Posts
    267
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: get the first letter of each word in a string

    Mr jtakw
    oh!
    I am using Excel 2013 version..
    thank you for the info..

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •