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.

Yes, there is a function in Excel XP that can extract the first letter of each word to create an acronym. The function is called LEFT() and it can be used in combination with other functions to achieve the desired result.

Here are the steps to extract the first letter of each word in Excel XP:

1. Open a new or existing Excel worksheet.
2. Type the text you want to create an acronym for in a cell. For example, "I am a boy" in cell A1.
3. In the adjacent cell, type the following formula: =LEFT(A1,1)&LEFT(MID(A1,FIND(" ",A1)+1,LEN(A1)),1)&LEFT(MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,LEN(A1)),1)&LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1)),1)
4. Press Enter to see the result. The formula will extract the first letter of each word in the text and combine them to form an acronym. In this case, the result will be "IAAB".
5. To create an acronym for multiple cells at once, you can drag the formula down to apply it to all the cells.

Alternatively, you can create a macro to automate the process. Here's an example of a macro that will create an acronym for the selected cell:

1. Press Alt + F11 to open the Visual Basic Editor.
2. Click on Insert Module to create a new module.
3. Copy and paste the following code into the module:

4. Press F5 or click on the Run button to run the macro.
5. Select the cell or range of cells you want to create an acronym for.
6. Click on the Macros button in the Developer tab and select the CreateAcronym macro.
7. The macro will extract the first letter of each word in the selected cells and create an acronym in the adjacent cell.

The long way around would be to do Data TextToColumns space
delimited....to separate each word into it's own column...........then to
CONCATENATE the
=LEFT(CELL,1) of each of those cells.....

Vaya con Dios,
Chuck, CABGx3



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

The following macro will place the result in the cell to the right of the
cell you are testing.

Sub Shorten()
Dim T As Range, I As Integer, myWord As String
E = ActiveCell
myWord = Left(E, 1)
For I = 2 To Len(ActiveCell.Value)
If Mid(E, I, 1) = " " Then
myWord = myWord & Mid(E, I + 1, 1)
End If
Next I
ActiveCell.Offset(0, 1) = myWord
End Sub

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

The following modification of my previous macro will allow you to select a
column of item and it will put the results in the column to the right for all
the selected cells.

Sub Shorten()
Dim T As Range, I As Integer, myWord As String
For Each cell In Selection
E = cell
myWord = Left(E, 1)
For I = 2 To Len(cell)
If Mid(E, I, 1) = " " Then
myWord = myWord & Mid(E, I + 1, 1)
End If
Next I
cell.Offset(0, 1) = myWord
Next cell
End Sub

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

Cool Shane, and it works well in XL97 too............

Vaya con Dios,
Chuck, CABGx3



"ShaneDevenshire" wrote:

The following macro will place the result in the cell to the right of the
cell you are testing.

Sub Shorten()
Dim T As Range, I As Integer, myWord As String
E = ActiveCell
myWord = Left(E, 1)
For I = 2 To Len(ActiveCell.Value)
If Mid(E, I, 1) = " " Then
myWord = myWord & Mid(E, I + 1, 1)
End If
Next I
ActiveCell.Offset(0, 1) = myWord
End Sub

--
Chees,
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.

Thanks for the macro.
Is it possible to create a UDF (custom function) instead so I can use it
anywhere and can do dynamic update?

A1: I am a boy
B1: Acronym(A1)
B1 answer is IAAB

If I change the cell in A1, the function will auto-update itself.
Thanks a lot.


"ShaneDevenshire" wrote:

Hi,

The following modification of my previous macro will allow you to select a
column of item and it will put the results in the column to the right for all
the selected cells.

Sub Shorten()
Dim T As Range, I As Integer, myWord As String
For Each cell In Selection
E = cell
myWord = Left(E, 1)
For I = 2 To Len(cell)
If Mid(E, I, 1) = " " Then
myWord = myWord & Mid(E, I + 1, 1)
End If
Next I
cell.Offset(0, 1) = myWord
Next cell
End Sub

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

Bug

For example:
Phantom Client (Reserved)

I expect:
PCR
or
PC(R)

[Note: I prefer the latter although both are ok]

However it turns out to be:
PC(



"ShaneDevenshire" wrote:

Hi,

The following modification of my previous macro will allow you to select a
column of item and it will put the results in the column to the right for all
the selected cells.

Sub Shorten()
Dim T As Range, I As Integer, myWord As String
For Each cell In Selection
E = cell
myWord = Left(E, 1)
For I = 2 To Len(cell)
If Mid(E, I, 1) = " " Then
myWord = myWord & Mid(E, I + 1, 1)
End If
Next I
cell.Offset(0, 1) = myWord
Next cell
End Sub

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

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.

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 -

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 -

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 -

On Wed, 16 Jan 2008 09:56:04 -0800, 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.

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

On Sat, 19 Jan 2008 16:00:33 -0500, Ron Rosenfeld
wrote:

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.

Note that changing one line will ensure that a <space is required between
words, but will ignore other potential word separators, and also insist that
the first character be a letter or digit:

=======================
re.Pattern = "([A-Z0-9]).*?(\s+[\W_]*|([\W_]*\s+)[\W_]?|$)"
=======================
--ron

Hello.

I'm really thankful I found your solution.

I'd like to know the best practice to make it ignore middle letters for example: Bureau of Investigation, originally it would build BOI. I'd like to ignore the word "of" and have only "BI".

Thank you very much.

Juan

On Wednesday, January 16, 2008 at 9:56:04 AM UTC-8, 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, Is there a way to create acronyms in Google Sheets?

wrote:

On Wednesday, January 16, 2008 at 9:56:04 AM UTC-8, 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, Is there a way to create acronyms in Google Sheets?

This is the easy way: One word per cell and use the left() function.

Example :
A B C D
1 I am a boy

I have a French version of Excel
=MAJUSCULE(GAUCHE(A1;1)&GAUCHE(B1;1)&GAUCHE(C1;1)& GAUCHE(D1;1))

I suppose that in English it should be sommething like
=CAPITAL(LEFT(A1;1)&LEFT(B1;1)&LEFT(C1;1)&LEFT(D1; 1))

And it should work in any spreadsheet software.



--
Vie : n.f. maladie mortelle sexuellement transmissible
Benoit chez lui à leraillez.com

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

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

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)