get the first letter of each word in a string

[LFKim2018]

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

 

[LFKim2018]

Mr. jtakw

one more favor - what if I want to limit the letters to just the first 2 or 3
how would I adjust your formula?
many many thanks

 

[Rick Rothstein]

Mr. jtakw

one more favor - what if I want to limit the letters to just the first 2 or 3
how would I adjust your formula?
many many thanks

Place it inside a call to the LEFT function.

 

[Peter_SSs]

I am using Excel 2013 version..

OK, so for a formula solution you would have to use my C1 formula or jtakw's.

 

[Peter_SSs]

what if I want to limit the letters to just the first 2 or 3

For my formula for

1 letter use red
2 letters use red & blue
3 letters use red, blue & green
etc
=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)

 

[Michael M]

You could also simply adjust the function provided by changing the numbers in red to whatever you need

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), [color=red]1[/color])
        Next I
    Else
        GFL = Left(arr, [color=red]1[/color])
    End If
End Function

 

[jtakw]

what if I want to limit the letters to just the first 2 or 3
how would I adjust your formula?
many many thanks

I'm not entirely sure what you mean...
We can reduce (cut parts, use only parts of) my formula like PeterSSs suggestion above, or do you mean you want the formula to be User assignable for Number of Characters to extract?

If it's the latter, you can do this:

C1 formula references D1 (User Input) to decide how many characters to extract, enter any number between 1 to 5, if left blank, will return the first character only.

Book1
	A	B	C	D
1	The Greatest Show On Earth	TGSOE	TG	2
2	Just Another Sample	JAS	JA
Sheet255

Cell Formulas
Range	Formula
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)
C1	=LEFT(A1,1)&IF(D$1>=2,LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),100,100)),1),"")&IF(D$1>=3,LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),200,100)),1),"")&IF(D$1>=4,LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),300,100)),1),"")&IF(D$1>=5,LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),400,100)),1),"")

 

[sandy666]

Another option, you can try with PowerQuery aka Get&Transform

Source		Result
Pearl Harbor		PH
What will be it will be		WWBIW
Hello world		HW
To be or not to Be		TBONT
Alpha and Omega		AAO
Mark John Susan Dimitr Andrew Oracle IBM Dynasty		MJSDA
SSD		S
The Greatest Show On Earth		TGSOE

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SplitResult = Table.SplitColumn(Source, "Source" , Splitter.SplitTextByDelimiter(" "), 5 ),
    #"Extracted First Characters" = Table.TransformColumns(SplitResult, {{"Source.1", each Text.Start(_, 1), type text}, {"Source.2", each Text.Start(_, 1), type text}, {"Source.3", each Text.Start(_, 1), type text}, {"Source.4", each Text.Start(_, 1), type text}, {"Source.5", each Text.Start(_, 1), type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Extracted First Characters",{"Source.1", "Source.2", "Source.3", "Source.4", "Source.5"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Result"),
    #"Uppercased Text" = Table.TransformColumns(#"Merged Columns",{{"Result", Text.Upper, type text}})
in
    #"Uppercased Text"[/SIZE]

Text can be longer than 5 words but PQ cut it just to 5

 

[LFKim2018]

Mr. Michael M, jtakw, Peter_SSs

you all have been very helpful.
thank you very very much..

 

[LFKim2018]

Mr. Rothstein
nice trick - though the formula would be quite long...
many thanks

 

[jtakw]

You're quite welcome, happy to help.