Creating Abbreviations with Excel VBA

The problem

Recently I was asked how hard would it be to take a company name and create the abbreviation of the name. For instance ‘International Business Machines’ would return IBM. The gentleman’s question involved dozens of company names and this code would be used regularly.

Objective

• For this project I choose to use a function because the input (company name) is in Excel and all we need to do is the output.
• If the name of the company is a single word then the function should return that name only.

There are a couple ways to extract the first letter of a string of words and concatenate them together. After a few minutes of consideration, I opt for the Split() VBA function.

The heart of the solution

The Split function is a built-in VBA string function and there is nothing hard about using it. As its name implies it divides or break into parts a text string based on the specified delimiter. The delimiter is a string that determine how to separate the original string into smaller pieces. For example, you can split the string “Animal,Flower,Birds,Vegetable” using the delimiter “,” to produce an array containing the four individual words (without the comma). In the case at hand, the delimiter would be a space (” “).

An array?

An array is simply a collection of items. We could make it more complex but this is not the purpose of this project. To refer to an array, you need to create a variable then refer to each element of the array by using its index. In the world of arrays the first element uses the index number 0 (zero). The second element will use the index number 1, and so on.

To know how many elements are stored in the string array, use the Ubound() VBA function. Ubound stand for Upper bound and will return the last index number in  the array. If the array contains 12 elements then the last (or highest) index number will be 11 (remember that the first element has an index of 0). Although this sounds weird at first, as you will see, this will make our job simpler.

Follow these steps to create an array in the Immediate Windows in Excel.

• Hit Alt+F11 to open the Visual Basic Editor (VBE).
• Hit Ctrl+G to open the Immediate Window.
• In the Immediate window type the expression: c=split(“Junior Reserve Officer Training Corps”, ” “) and hit Enter. Nothing happened? Good. This means that all went well. The space in double quotation marks after the comma specifies the delimiter used to ‘split’ the string into individual strings.
• The letter ‘c’ at the beginning of the expression will be our variable.
• Type the following expression and hit Enter.
• ?c(0) (meaning return the value of the 1st element in the array c.)
• The expression above returns: Junior without the space.
• Typing ?c(1) would return ‘Reserve’, and so on.
• Type ?Ubound(c) (This would return 4 so the original string had 5 words.)
• All we need now is a loop that will cycle through the array and process each element as required.

How many times to loop?

When using a loop, we must know how many times the loop has to … loop! What was the value returned by the expression Ubound(c) used earlier in the Immediate Window? It was 4 because the array contains 5 elements. That is exactly what we need to control the loop.

So a loop that would look like the following would print each word in the Immediate Window:

For j = 0 To i
     strAbbr = strAbbr & UCase(Left(Company(j), 1))
Next j

The line of code inside the loop is beginners VBA code. Let’s start by looking at what the loop does.

The variable i contains the highest element number in the array (4 in this case). The variable j will take on the values 0, 1, 2, 3 and 4 hence the loop will iterate 5 times. Let’s look at the deepest instruction in the brackets above. In the expression Company(j), Company is our array (In the first example above we used C to keep things simple. So when the loop runs five times it reads: Company(0), Company(1), Company(2) … and so on until Company(4).

Remember that the company were using in our example is: “Junior Reserve Officer Training Corps”.

The bit with UCase(Left(Company(1), 1)) will extract one character from the left of each word in the company name and ensure that the letter is uppercase.

Element 0: Ucase(Left(Company(0), 1)) = J
Element 1: Ucase(Left(Company(1), 1)) = R
Element 2: Ucase(Left(Company(2), 1)) = O
Element 3: Ucase(Left(Company(3), 1)) = T
Element 4: Ucase(Left(Company(4), 1)) = C

In the loop we need to concatenate each first letter with all the previous letters. So the first time the result is only a J. then we concatenate the second to the previous letters to have JR. Again a third time to have JRO, etc. Remember though that if the organisation is made on one word only then the function returns the full name instead of the first letter. Download a PDF discussing a good introduction to loops in VBA

Below is the final function. The comments help understand what is going on.

Function Acronym(strCompany As String) As String
' Return the abbreviation for the supplied string.

     Dim Company() As String    ' Company name array
     Dim i As Byte, j As Byte   ' Number of words and counter.
     Dim strAbbr As String      ' String of abbreviation.

     ' Create array of words.
     Company() = Split(strCompany, " ")
     i = UBound(Company())      ' Total number of words

     If i > 0 Then              ' If more than one element.
         For j = 0 To i         ' Loop through all elements.
             strAbbr = strAbbr & UCase(Left(Company(j), 1))
         Next j
     Else
         strAbbr = strCompany   ' If one word only, return the word.
     End If
     Acronym = strAbbr          ' Value returned by the function.
End Function

Suppose we run that function with the string: “Junior Reserve Officer Training Corps”, each iteration of the loop would result in the following:

Loop 1: strAbbr = J
Loop 2: strAbbr = JR
Loop 3: strAbbr = JRO
Loop 4: strAbbr = JROT
Loop 5: strAbbr = JROTC

However if you pass a single word to the function it will return that word because you don’t want to abbreviate one word! So:

Acronym("Charlie")

would return

Charlie

Download the zip file with the workbook (with some company names) as well as the function in used in this tutorial.

Hope you liked this project. This is the type of challenge you would find in the real world.

Daniel from ComboProjects