On Thursday, January 17, 2008 at 5:55:01 AM UTC+11, 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.

Hi Shane, long time since you posted this I can see, but I'm wondered where do you put this? I tried putting it into the script editor but it has a problem with the first line. I would like to create an acronym of a word and this seems to be the best
response I've found but I'm unable how to use it!

Thanks

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

The following function does what you want. Just copy/paste the code into a standard module in VBE under the workbook you want to use it.

Option Explicit

Function MakeAcronym$(sWordsIn$)
' Returns a string of 1st character of each word in sWordsIn
' converted to uppercase
Dim vWord, s1$
For Each vWord In Split(sWordsIn, " ")
s1 = s1 & Mid(vWord, 1, 1)
Next 'vWord
MakeAcronym = UCase$(s1)
End Function

To use it in a worksheet formula in that workbook where words are in colA, in the col where you want the acronym type the following formula:

=MakeAcronym(A1) (Revise the cell ref to suit the row#)

You can also use the function in VBA as follows, for example:

Sub Doit_1()
' Puts the acronym into a string variable to be used in VBA
Dim s1$
s1 = MakeAcronym(ActiveCell.Value)
'do something with s1
Debug.Print s1
End Sub

Sub Doit_2()
' Inserts the acronym in the next col
ActiveCell.Offset(0, 1) = MakeAcronym(ActiveCell.Value)
End Sub

Sub Doit_3()
' Inserts acronym in next col of selected cells
' (cells do not need to be contiguous)
Dim vRng
For Each vRng In Selection.Cells
vRng.Offset(0, 1) = MakeAcronym(vRng.Value)
Next 'vRng
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Wednesday, January 16, 2008 at 11:26:04 PM UTC+5:30, 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.

=left(cell no,no of letter u want to extract)

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)