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

 

[Michael M]

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

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

 

[Peter_SSs]

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.

Excel Workbook
	A	B	C
1	The Greatest Show On Earth	TGSOE	TGSOE
2
3	Today is Friday	TiF	TiF
First letters

 

[jtakw]

Hi,

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

Book1
	A	B
1	The Greatest Show On Earth	TGSOE
2	Just Another Sample	JAS
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)

 

[Peter_SSs]

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

Excel Workbook
	A	B	C	D
1	The Greatest Show On Earth	TGSOE	TGSOE	TGSOE
2
3	Today is Friday	TiF	TiF	TiF
First letters

 

[LFKim2018]

Mr. Michael M
thank you for your reply..

 

[LFKim2018]

Mr. jtakw
thank you for your assistance..

 

[LFKim2018]

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

 

[jtakw]

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.

 

[LFKim2018]

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