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

 

[Rick Rothstein]

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

What do you mean by this... that you see the formula displayed in the cell? If not, please explain further. If so, change the cells' formatting from Text to General then re-enter the formula in C9 so that it calculates, then copy it down to C18.

 

[Brutium]

Thank you Rick, It works!!!!!

But how do I modify the Module if I want to get from Our Lady Of Lourdes the Code OLL, and from Our Lady of Perpetual Help the Code OLPH?

 

[Rick Rothstein]

Thank you Rick, It works!!!!!

But how do I modify the Module if I want to get from Our Lady Of Lourdes the Code OLL, and from Our Lady of Sorrows the CodeOLS?

It would have helped if you had told me what you are seeing now. I am going to guess that you are only seeing OL for "Our Lady of Sorrows". If that is the case, it is because you have a space character as the end of that name... since space characters are the delimiters between words, that trailing space makes the code think that "Sorrows" is not the last word. You can straighten out you data so it does not end with spaces and the code I gave you will work; however, if for some reason you do not want to do that, then use this UDF instead of the one I gave you earlier...

Function Encode(S As String) As String
  Dim Words() As String
  Words = Split(Application.Trim(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

 

[Brutium]

Rick, I do get the correct Code for Our Lady of Sorrows as OLS, but I need to have OLPH for the school Our lady of Perpetual Help, and what can I do to avoid having two schools having the same Code if they have the same initials e.g. St. Clare as STC and St. Columba also as STC, would it be possible to have (when two schools have the same initials) a Code which is made up of the fist initial and then the two letters of the second word e.g. St. Clare would be SCL and St. Columba the Code SCO?

 

[Rick Rothstein]

but I need to have OLPH for the school Our lady of Perpetual Help

I can work on avoiding duplicate codes, but first I want to straighten out the above. You said in a response to Andrew that 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" which, when coupled with your first message seemed to indicate you wanted 3-letter codes; however, the above statement from your last message appears that you want the same number of letters as there are words (excluding in this case the small word "of" but including the small word "the" in "Christ the King"). Can you supply us with an exact specification for how you want you codes formed (keeping in mind that computers need exact rule in order to encode them in programming code)?

 

[Brutium]

When I first formulated my reques,t I was happy with having only three letters, then, talking to colleagues I was reminded that there are some schools that would have the same possible three letters for a Code, and, that there are some schools that identify themselves with a four letter Code, and that is the reason for the four letter Code. The majority of our schools are OK with a three letter Code but there a number of schools where I need to have four letter Codes and a number of schools that might have the same three letter codes, which I need to avoid by creating a different possible Code. And that is why I was suggesting having the first letter of the name and the two first letters of the second part of the name.

 

[Rick Rothstein]

When I first formulated my reques,t I was happy with having only three letters, then, talking to colleagues I was reminded that there are some schools that would have the same possible three letters for a Code, and, that there are some schools that identify themselves with a four letter Code, and that is the reason for the four letter Code. The majority of our schools are OK with a three letter Code but there a number of schools where I need to have four letter Codes and a number of schools that might have the same three letter codes, which I need to avoid by creating a different possible Code. And that is why I was suggesting having the first letter of the name and the two first letters of the second part of the name.

Some schools need a 4-letter code, some don't... I don't see a rule here that can be made into programming code. Unlike humans, computers can't think and evaluate situation on their own... they need well defined, fixed, repeatable algorithms with well-defined exceptions to the "normal" rules in order to perform their magic. You are going to have to lock things down a lot tighter than you have to date if you want a coded solution to your problem (and with the variations you have mentioned so far, I am nearly 100% sure a formula solution will not be possible).

 

[Brutium]

What about increasing the number of letters in the Code? So if a school has three letters as it is now that would be one solution and the forth letter would be a blank, if the school has four parts to its name, as in the case of Our Lady of Perpetual Help, then the four letters would be the initial letters of each word.
What do you think?

 

[Rick Rothstein]

What about increasing the number of letters in the Code? So if a school has three letters as it is now that would be one solution and the forth letter would be a blank, if the school has four parts to its name, as in the case of Our Lady of Perpetual Help, then the four letters would be the initial letters of each word.
What do you think?

I can code it anyway you want (although I would think if there were three parts to a school's name, there would be only three letters without a trailing blank), you just have to tell me what it is that you want. You have to remember when asking questions in a forum that the people who well be responding to you will have no knowledge of the "business model" governing your data, the data itself, the layout of that data, nor what you need/want to do with the data... we only know what you tell us and nothing more.

 

[Brutium]

Once again, Rick I would like to thank you for your patience and generosity. I know that I am being a little difficult, but the reason is that I know exactly what I need, but it is rather difficult to explain and make anybody understand what is that I want to accomplish. Said all that let me try again...

I have a list of school's names. I need to create codes that would identify each school by just looking at the code. So if I see OLPH I understand that the acronym stands for Our Lady of Perpetual Help. If I see a school code such as CTK, I will know that this stsnds for Christ the King etc. The problem arises when I have schools that might have the same three letters in their names. e.g. St. Cecilia and St. Clare or St. Clement. these schools I would like to identify respectively with SCE, SCL, ....this will not work....
I think that I will have to change the default coding for each school so that instead of three letters will have to be four letters, so the schools will be identified as follows:
St. Cecilia, SCEC
St. Clement, SCLE
St. Clare, SCLA
Our Lady of Perpetual Help OLPH
Is this feasible? can the coding be changed?

Once again THANK YOU!!!