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

1. 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  Reply With Quote

2. 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  Reply With Quote

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

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

 Cell Formula 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  Reply With Quote

4. 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)  Reply With Quote

5. Re: get the first letter of each word in a string Originally Posted by jtakw 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

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

 Cell Formula 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  Reply With Quote

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

Mr. Michael M  Reply With Quote

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

Mr. jtakw
thank you for your assistance..  Reply With Quote

8. 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  Reply With Quote

9. 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.  Reply With Quote

10. 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..  Reply With Quote

User Tag List

Tags for this Thread

excel 2013, formula, string, vba, word  Posting Permissions

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