ACRONYM OF CAPS ONLY
 

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
--

Best Regards,
FARAZ A. QURESHI

ACRONYM OF CAPS ONLY
 

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

--
__________________________________
HTH

Bob

"FARAZ QURESHI" wrote in message
...
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
--

Best Regards,
FARAZ A. QURESHI

ACRONYM OF CAPS ONLY
 

FARAZ QURESHI wrote...
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.

ACRONYM OF CAPS ONLY
 

On Mon, 23 Feb 2009 00:08:03 -0800, FARAZ QURESHI
wrote:

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