The following function does what you want. Just copy/paste the code into a
standard module in VBE under the workbook you want to use it.

Option Explicit

Function MakeAcronym$(sWordsIn$)
' Returns a string of 1st character of each word in sWordsIn
' converted to uppercase
Dim vWord, s1$
For Each vWord In Split(sWordsIn, " ")
s1 = s1 & Mid(vWord, 1, 1)
Next 'vWord
MakeAcronym = UCase$(s1)
End Function

To use it in a worksheet formula in that workbook where words are in colA, in
the col where you want the acronym type the following formula:

=MakeAcronym(A1) (Revise the cell ref to suit the row#)

You can also use the function in VBA as follows, for example:

Sub Doit_1()
' Puts the acronym into a string variable to be used in VBA
Dim s1$
s1 = MakeAcronym(ActiveCell.Value)
'do something with s1
Debug.Print s1
End Sub

Sub Doit_2()
' Inserts the acronym in the next col
ActiveCell.Offset(0, 1) = MakeAcronym(ActiveCell.Value)
End Sub

Sub Doit_3()
' Inserts acronym in next col of selected cells
' (cells do not need to be contiguous)
Dim vRng
For Each vRng In Selection.Cells
vRng.Offset(0, 1) = MakeAcronym(vRng.Value)
Next 'vRng
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion