The Trim function was meant to stop that happening, but it works
differently than in a worksheet. This version clears up the multi-
space errors:

Function Acronym(phrase As String) As String
Dim i As Integer
Dim ch As String, words As String
Acronym = ""
phrase = Trim(phrase)
If Len(phrase) < 1 Then End
words = ""
For i = 1 To Len(phrase)
ch = UCase(Mid(phrase, i, 1))
If ch = "-" Or ch = "/" Then ch = " "
If InStr(" ABCDEFGHIJKLMNOPQRSTUVWXYZ", ch) 0 Then
words = words & ch
End If
Next i
If (Len(words) < 1) Then End
Acronym = Left(words, 1)
For i = 2 To Len(words)
ch = Mid(words, i, 1)
If ch = " " Then
Acronym = Acronym & Mid(words, i + 1, 1)
End If
Next i
words = Acronym
If Len(Acronym) 1 Then
Acronym = Left(words, 1)
For i = 2 To Len(words)
ch = Mid(words, i, 1)
If ch = " " Then ch = ""
Acronym = Acronym & ch
Next i
End If
End Function

But, keep testing it...

Pete

On Jan 17, 6:23*pm, VB_Sam wrote:
Thanks.

There are some minor bugs.
John / Mary
Phrases with more than one space, eg:
Litter___Go___Ride

_ is a space in this case.

Expected:
JM
LGR

It turns out to be:
J M
L__G__R

One code should be added to remove all space after you finish extracting all
first letters.



"Pete_UK" wrote:
This will give you just characters in your acronym:

Function Acronym(phrase As String) As String
* * Dim i As Integer
* * Dim ch As String, words As String
* * Acronym = ""
* * phrase = Trim(phrase)
* * If Len(phrase) < 1 Then End
* * words = ""
* * For i = 1 To Len(phrase)
* * ch = UCase(Mid(phrase, i, 1))
* * If ch = "-" Or ch = "/" Then ch = " "
* * If InStr(" ABCDEFGHIJKLMNOPQRSTUVWXYZ", ch) 0 Then
* * * * words = words & ch
* * End If
* * Next i
* * If (Len(words) < 1) Then End
* * Acronym = Left(words, 1)
* * For i = 2 To Len(words)
* * * * ch = Mid(words, i, 1)
* * * * If ch = " " Then
* * * * * * Acronym = Acronym & Mid(words, i + 1, 1)
* * * * End If
* * Next i
End Function

Put your phrase in A1, and use it as:

=Acronym(A1)

It produces PCOSR from Phantom-Client Ocean/Sea (Reserved!), as it
treats a hyphen and forward slash as if they were a space. The acronym
will always be upper case.

Hope this helps.

Pete

On Jan 17, 3:44 am, VB_Sam wrote:
Thanks. It works. But there is one problem.

For example:
Phantom-Client Ocean/Sea (Reserved!)

Expected result:
PCOSR or PCO/S(R)

Actual result:
PO(

Is it possible to have a fix?

Perhaps add a code to remove all punctuation/symbols before it proceed:

Pseudo-code:
Read "Phantom-Client Ocean/Sea (Reserved!)"
Replace "-" or "/" with a space. Output: "Phantom Client Ocean Sea
(Reserved!)"
Remove any symbol found. Output: "Phantom Client Ocean Sea Reserved"
Extract the first letter of each word. Output: "PCOSR"

Thanks a lot.

"ShaneDevenshire" wrote:
Hi again,

If you want a spreadsheet function to do this:

Function Ext(myText As String) As String
* * Dim I As Integer, myWord As String
* * * * myWord = Left(myText, 1)
* * * * For I = 2 To Len(myText)
* * * * * * If Mid(myText, I, 1) = " " Then
* * * * * * * * myWord = myWord & Mid(myText, I + 1, 1)
* * * * * * End If
* * * * Next I
* * * * Ext = myWord
End Function

then in any cell type =Ext(A1)

where A1 contains the text you want to operate on.

Note: in my previous macro I dimmed T but I didn't use it, you could remove
it from the Dim statement line if you wish.

--
Cheers,
Shane Devenshire

"VB_Sam" wrote:

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.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -