How can I extract first letter of each word in Excel XP?
For example:
I am a boy
You are a girl
Using the pseudo-function called acronym(), the result will become:
IAAB
YAAG
I'm using Excel XP.
Is there any function which can do it?
If not, could anyone provide a macro for me?
(I'm only a beginner in macro)
Thanks.
It would be best if you could give all your requirements at once.
For example, for the problem you pose above, with your examples showing only
space-delimited words, there is a very simple VBA solution:
==============================
Function Split1(str As String) As String
Dim sTemp() As String
Dim i As Long
sTemp = Split(Application.WorksheetFunction.Trim(str))
For i = 0 To UBound(sTemp)
Split1 = Split1 & UCase(Left(sTemp(i), 1))
Next i
End Function
====================================
But then you add a parameter that the first letter of a word might be enclosed
in parentheses:
----------------------
Phantom Client (Reserved)
I expect:
PCR
or
PC(R)
-----------------------------
Then, in another message, you add a requirement that some character in addition
to a space might be between the two words:
---------------------------------
John / Mary
Phrases with more than one space, eg:
Litter___Go___Ride
----------------------------
The following UDF will take care of all the examples you've given, but if you
have more and different requirements, please try to post them all at once:
=============================================
Function Acronym(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "(\w).*?(\W+|\s+|$)"
Acronym = UCase(re.Replace(str, "$1"))
End Function
============================================
But even this might not handle the following in the manner in which you expect:
John/Mary --> JM
John_Mary --> J
This can be easily changed, but you need to be more specific as to what you
really want. Rather than just giving examples, you need to devise rules that
will work for all cases.
--ron
