ACRONYM OF CAPS ONLY

[FARAZ QURESHI]

I want 2 develop a UDF like Acro, which would return the acronymn of only the
letter in Caps. For example
=ACRO(A1)
when A1 is:
"United States of America"
would return:
USA
and NOT:
USOA

 

[Bob Phillips]

Public Function ACRO(cell As Range)
Dim i As Long
Dim tmp As String
If cell.Count > 1 Then ACRO = CVErr(xlErrRef)
For i = 1 To Len(cell.Value)

If Mid$(cell.Value, i, 1) <> " " Then

If Mid$(cell.Value, i, 1) = UCase(Mid$(cell.Value, i, 1)) Then

tmp = tmp + Mid$(cell.Value, i, 1)
End If
End If
Next i
ACRO = tmp
End Function

 

[Harlan Grove]

I want 2 develop a UDF like Acro, which would return the acronymn of only the
letter in Caps. For example
=ACRO(A1)
when A1 is:
"United States of America"
would return:
USA
and NOT:
USOA

Download and install Laurent Longre's MOREFUNC.XLL add-in and use its
REGEX.SUBSTITUTE function. The formula

=REGEX.SUBSTITUTE("United States of America","[^A-Z]","")

returns USA. If only Excel provided this as a built-in.

 

[Ron Rosenfeld]

I want 2 develop a UDF like Acro, which would return the acronymn of only the
letter in Caps. For example
=ACRO(A1)
when A1 is:
"United States of America"
would return:
USA
and NOT:
USOA

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like =ACRO(A1) in some
cell.



================================
Option Explicit
Option Compare Binary
Function ACRO(str As String) As String
Dim i As Long
Dim sTemp As String

For i = 1 To Len(str)
sTemp = Mid(str, i, 1)
If sTemp Like "[A-Z]" Then
ACRO = ACRO & sTemp
End If
Next i

End Function
=========================
--ron