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]

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?

The main problem here is you are not a programmer, so you do not see the problems from the same standpoint as I do. For example...

What should happen if you had the names "St. Clare" and "St. Clarus"?

What should happen if you had the names "Our Lady of Perpetual Help" and "Our Lady of Perpetual Hope"?

The program cannot intuitively code around the above type of "collisions", so I have to provide the workarounds in code for them, but I don't know all the facts about why you are doing this and how you expect to see the abbreviation per school, so you need to provide me with a fixed set of rules as to how you would assign your abbreviations for the "not standard" situations that could arise down the line so that I can encapsulate them in code for you. Otherwise, you risk the code crashing if such an unhandled situation should occur.

 

[Brutium]

Thank God we do not have these other saints, but in that eventuality would it be possible to add a number after the acronym? such as SCLA1 and SCLA2?

 

[Rick Rothstein]

Thank God we do not have these other saints, but in that eventuality would it be possible to add a number after the acronym? such as SCLA1 and SCLA2?

Perfect... that is a rule I can work with. I'll need a little time time to create some code for you, but I need to know where you are going to keep your list of schools at (what sheet name and what column and what starting row)?

 

[Brutium]

The schools name are found on Sheet 3 starting at B9

 

[Rick Rothstein]

The schools name are found on Sheet 3 starting at B9

Okay, give this macro a try...

Sub AssignSchoolAbbreviation()
  Dim X As Long, LastRow As Long, Dupes As Long
  Dim Code As String, Words() As String
  Dim ListWS As Worksheet, CodeWS As Worksheet
  Const ListSheet As String = "Sheet3"
  Const ListColumn As String = "B"
  Const ListStartRow As Long = 9
  Const CodeSheet As String = "Sheet3"
  Const CodeColumn As String = "A"
  Const CodeStartRow As Long = 9
  Set ListWS = Worksheets(ListSheet)
  Set CodeWS = Worksheets(CodeSheet)
  LastRow = ListWS.Cells(Rows.Count, ListColumn).End(xlUp).Row
  For X = ListStartRow To LastRow
    Words = Split(ListWS.Cells(X, ListColumn).Value)
    Select Case UBound(Words)
      Case 0
        Code = Left(Words(0), 4)
      Case 1
        Code = Left(Words(0), 1) & Left(Words(1), 3)
      Case 2
        Code = Left(Words(0), 1) & Left(Words(1), 1) & Left(Words(2), 2)
      Case Else
        Code = Left(Words(0), 1) & Left(Words(1), 1) & Left(Words(2), 1) & Left(Words(3), 1)
    End Select
    Code = UCase(Code)
    If X > ListStartRow Then Dupes = WorksheetFunction.CountIf(CodeWS.Cells(CodeStartRow, _
                                     CodeColumn).Resize(X - ListStartRow), Code & "*")
    If Dupes Then Code = Code & (Dupes + 1)
    CodeWS.Cells(CodeStartRow + X - ListStartRow, CodeColumn).Value = Code
  Next
End Sub

Notes
----------
1. The first instance of a name with duplicates will appear without a number.

2. The second instance of a name with duplicates will have a 2 affixed to it, third instance a 3, etc.

3. IMPORTANT - The order for the raw list of school name must not be change, simply affix any new names to the bottom of the existing list. If you change the order of the names after they have been entered, you risk changing the assigned codes for the schools. So, if you ever need a sorted list of some kind, copy the school and code columns to a set of columns and sort it there,

4. I forgot to ask you where you wanted the code outputted to, so I assumed Column A on Sheet3 next to the school list. If this guess was wrong, you can change the output by changing the assignment CodeSheet, CodeColumn and CodeStartRow constants (last three Const statements at the top of the code).