Acronyms in Excel

When you want to create acronyms in Excel, there are several approaches.

There are the entirely VBA-based solutions, such as those found at Better Solutions Acronym UDF or Excel VBA Macro's Acronym function.

Here is a formula that can do the same thing, but with one caveat: it only works on three-letter acronyms. In other words, it only works on cells with three words. It isn't as intelligent as the UDF from Better Solutions, but doesn't acquire the overhead that accompanies UDFs.

=LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1)&MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,1)

We can bake some intelligence into this formula, albeit awkwardly, by checking the number of words in the cell using the formula

=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1

and then using the CHOOSE to select the appropriate formula to apply. For example, the following formula creates acronyms for any phrase up to three words:

=CHOOSE(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1,LEFT(A1,1),LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1),LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1)&MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,1))

Acronyms in VBA

This VBA function (usable in any VBA program) takes an input string and tokenizes it, then returns an acronym based on the input phrase. It also allows you to specify a list of words to be ignored when building the acronym, as well as case sensitivity. Extra spaces are ignored. The input String is tokenized and parsed for letters. If the leftmost character of each token is alphabetic, it is added to the return String.

You can include a list of ignored words (for example "the","a","in") to skip when building the acronym.

Function GetAcronym(fullText As String, exactCase As Boolean, _
                    ParamArray wordsToExclude() As Variant) As String
Dim words() As String
Dim firstLetter As String
Dim i As Long, j As Long
Dim theWord As String
Dim tempWords() As String

  ' set exactCase = False when you don't want exact match on case
  ' set exactCase = True when you want case sensitivity

  words = Split(fullText)

  ' list of words to exclude?
  If Not IsMissing(wordsToExclude) Then
    ' create array of words to exclude
    ReDim tempWords(1 To UBound(wordsToExclude) + 1)

    ' check for case sensitivity
    If Not exactCase Then
      For j = LBound(tempWords) To UBound(tempWords)
        tempWords(j) = UCase$(wordsToExclude(j - 1))
      Next j
    Else
      For j = LBound(tempWords) To UBound(tempWords)
        tempWords(j) = wordsToExclude(j - 1)
      Next j
    End If
  End If

  ' loop through input string
  For i = LBound(words) To UBound(words)

    ' check for case sensitivity
    If Not exactCase Then
      theWord = UCase$(words(i))
    Else
      theWord = words(i)
    End If

    If Not IsMissing(wordsToExclude) Then
      ' if given word is in list of words to exclude, do not include it in acronym
      If UBound(Filter(tempWords, theWord)) = -1 Then
        ' capitalize first letter
        firstLetter = UCase$(Left$(theWord, 1))
        ' if it's alphabetic, add to string
        If firstLetter Like "[A-Z]" Then
          GetAcronym = GetAcronym & firstLetter
        End If
      End If
    Else ' skip the ignored words check
      ' capitalize first letter
      firstLetter = UCase$(Left$(theWord, 1))
      ' if it's alphabetic, add to string
      If firstLetter Like "[A-Z]" Then
        GetAcronym = GetAcronym & firstLetter
      End If
    End If
  Next i

End Function

Paste into a standard module and call the procedure, passing in an input string. Ex: Debug.Print GetAcronym("British Broadcasting Corporation")

You can also use the function as a UDF directly on the worksheet.

Sample Usage:

Sub TestGetAcronym()

' case sensitive
' "The" will be included when case does not match, only excluded when case matches
Debug.Print GetAcronym("The British Broadcasting Corporation", True, "The", "a", "and", "it")   ' returns BBC
Debug.Print GetAcronym("The British Broadcasting Corporation", True, "the", "a", "and", "it")   ' returns TBBC
' case insensitive
' regardless of how "The" is spelled, it will be excluded because it matches
Debug.Print GetAcronym("The British Broadcasting Corporation", False, "The", "a", "and", "it")   ' returns BBC
Debug.Print GetAcronym("The British Broadcasting Corporation", False, "the", "a", "and", "it")   ' returns BBC

' case sensitivity irrelevant without ignored words list
Debug.Print GetAcronym("The British Broadcasting Corporation", True) ' returns TBBC
Debug.Print GetAcronym("The British Broadcasting Corporation", False) ' returns TBBC

End Sub

These examples show you how the function works. When case sensitivity is requested, words are excluded when they match the input string exactly. When the function is told to ignore case, matched words are always excluded regardless of case.

When no list of words to be ignored is included, the case sensitivity setting is ignored. This is because the case sensitivity setting only applies when a list of words to be ignored is included.

Download VBA function for Excel 2003
Download VBA function for Excel 2007/2010