Paste the below code and try running MyMacro or use that as a UDF

With the name in cell A1..try the below formula
=GetAcronym(A1)

Sub MyMacro()
MsgBox GetAcronym("Society for Maternal-Fetal Medicine")
End Sub

Function GetAcronym(strName As String) As String
Dim arrName As Variant
strName = " " & strName & " "
strName = Replace(strName, " of ", " ", , , vbTextCompare)
strName = Replace(strName, " for ", " ", , , vbTextCompare)
strName = Replace(strName, " the ", " ", , , vbTextCompare)
strName = Replace(strName, " and ", " ", , , vbTextCompare)
strName = Replace(strName, " a ", " ", , , vbTextCompare)
strName = Replace(strName, " on ", " ", , , vbTextCompare)
strName = Replace(strName, "-", " ", , , vbTextCompare)
arrName = Split(strName)
For intCount = 0 To UBound(arrName)
GetAcronym = GetAcronym & Left(arrName(intCount), 1)
Next
End Function

--
Jacob


"msnyc07" wrote:

I have a script that generates acronyms from company names. It was set it up
to ignore of, and, the, etc. (I didn't code it just spec'd it)

If Not (UCase(prom) = "OF") And Not (UCase(prom) = "FOR") And Not
(UCase(prom) = "THE") And _
Not (UCase(prom) = "AND") And Not (UCase(prom) = "A") And
Not (UCase(prom) = "ON") And Not (prom) = "-" Then

The problem is I need it to also ignore hyphens so for instance

Society for Maternal-Fetal Medicine

becomes

SMFM

I tried adding a hyphen to the code above (w/o UCASE) but it doesn't work.

Anyway I can do this without having to remove the hyphens themselves?