First Letter From Words VBA - Acronymize

[Rick Rothstein]

Try this in a code module, and use in formula

 
Function ACRONYMIZE(rng As Range) As String
    Dim lng As Long
 
    For lng = 0 To UBound(Split(Trim(rng.Text), " "))
        ACRONYMIZE = ACRONYMIZE & Left(Split(Trim(rng.Text), " ")(lng), 1)
    Next lng
 
End Function

Using the UBound function where I have highlighted in red does not help in making sure the acronyms returned by the function will be all upper case letters. Also, I think it would be better to use the WorksheetFunction's Trim function instead of the VB Trim function as that would reduce multiple contiguous spaces down to single spaces and avoid having to try to trim individual words in the loop. Finally, my preference for text processing functions is to make the text argument a String rather than a Range... doing this would allow the function to be more easily used by other VB code and still not affect the functionality when used as a UDF. This is how I probably would have written your ACRONYMIZE function...

Function ACRONYMIZE(S As String) As String
     Dim X As Long, Words() As String
     Words = Split(UCase(WorksheetFunction.Trim(S)))
     For X = 0 To UBound(Words)
         ACRONYMIZE = ACRONYMIZE & Left(Words(X), 1)
     Next
End Function