Excel VBA Macro

How to get an Acronym using Excel VBA?

Posted on the June 17th, 2009 under Excel Formula by Poer @ Excel VBA/Macro

The purpose of the following example of Excel VBA macro is to get the acronym or an abbreviation of each of the first letter of any given words.

Function Acronym(Words As Variant) As String

    Dim aWord() As String, ix As Integer

    aWord = Split(Words.Value, " ")
    For ix = 0 To UBound(aWord)
        Acronym = Acronym & UCase(Left(aWord(ix), 1))
    Next ix

End Function

Using Split function and space as delimiter, we create an array of word. Then using simple for … to loop, we do looping of each word and take the first letter of each word in array using Left function.

By using iteration and array like this, we will get the acronym of the word in question.

We can directly use the function in Excel formula bar with a reference to the cell that contains the collection of words we want to make an acronym from it as input.

For the details information of each function in use, Split/Ucase/Left, select each function and push F1 while in the Visual Basic Editor.

Related Entries

• Auto Expanding VLOOKUP Table Array
• Check If Excel Workbook is Already Open or Not
• Getting Cell Reference With Input Box
• Sum Unique/Distinct Values in Excel
• Excel Extract Cell Comments

External Resources

• Microsoft Excel 2003/2007 Video Tutorials

  Step-by-step video guide to mastering Charts, PivotTable, Data Analysis and Macro programming in Microsoft Excel in 5 hours.

• 101 Secrets of Microsoft Excel

  Discover 101 of Excels little-known secrets that have been hiding right under your nose.

Tagged with: acronym, array, excel macro example, excel vba example, left, split, ucase