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

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

Mr. jtakw

one more favor - what if I want to limit the letters to just the first 2 or 3
many many thanks

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

Originally Posted by LFKim2018
Mr. jtakw

one more favor - what if I want to limit the letters to just the first 2 or 3
many many thanks
Place it inside a call to the LEFT function.

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

Originally Posted by LFKim2018
I am using Excel 2013 version..
OK, so for a formula solution you would have to use my C1 formula or jtakw's.

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

Originally Posted by LFKim2018
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)

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

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

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```

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

Originally Posted by LFKim2018
what if I want to limit the letters to just the first 2 or 3
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.

ABCD
1The Greatest Show On EarthTGSOETG2
2Just Another SampleJASJA

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)
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),"")

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

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

Code:
```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"```
Text can be longer than 5 words but PQ cut it just to 5

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

Mr. Michael M, jtakw, Peter_SSs

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

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

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

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

You're quite welcome, happy to help.