Initial Characters from words

[Brutium]

Hello all,

I am trying to create code names for based on words. What I mean is select the initial letter of two three or more words.
so that:
Christ the King would be CTK
Holy Child would be HOC where the three letters of the code name would be made up of the first two letters of the first word and the first letter of the second.

Is this possible? I am using Excel 2010

Any help would be greatly appreciated.
Brutium

 

[Andrew Poulsom]

What would you want for 4 or more words?

 

[Brutium]

Thank you for your quick reply Andrew.

If there are more than three words I would be happy with the initials of the first two words and the initial of the last word.

 

[Andrew Poulsom]

Why not the same as for three words?

 

[Rick Rothstein]

If there are more than three words I would be happy with the initials of the first two words and the initial of the last word.

It looks like a formula solution would produce a long, complicated and ugly looking formula; so how about a UDF (user defined function)?

Function Encode(S As String) As String
  Dim Words() As String
  Words = Split(S)
  Select Case UBound(Words)
    Case 0: Encode = Left(S, 3)
    Case 1: Encode = Left(Words(0), 2) & Left(Words(1), 1)
    Case Else: Encode = Left(Words(0), 1) & Left(Words(1), 1) & Left(Words(UBound(Words)), 1)
  End Select
  Encode = UCase(Encode)
End Function

If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Encode just like it was a built-in Excel function. For example,

=Encode(A1)

 

[Brutium]

Because at times, the names of the schools (these are all schools names) are too long and usually the last the word is the more significant one. If it is too complicated, then I would accept the initials of the first three words just the same.

Thank you.

 

[Brutium]

Rick thank you for your help.
I did just as you said, but I am not sure where or how to address the user defined function. I should have explained myself better.
I have a table with the names of the schools, and a second table where I need to create the Codes for each school. So in table K9 tpo K18 I have the names of the various schools and the code which will be generated will have to appear in column C9 to C18. Does this make sense?

 

[Rick Rothstein]

Rick thank you for your help.
I did just as you said, but I am not sure where or how to address the user defined function. I should have explained myself better.
I have a table with the names of the schools, and a second table where I need to create the Codes for each school. So in table K9 tpo K18 I have the names of the various schools and the code which will be generated will have to appear in column C9 to C18. Does this make sense?

Just put this in C9 and copy it down to C18...

=Encode(K9)

 

[Brutium]

Rick that is exactly what I had done, but all I get is #VALUE! error.

 

[Brutium]

Correction Rick...
down the C column I get =Encode(K9) to (K18)