Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 16th 08, 05:56 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2008
Posts: 4
Default Create Acronym (Extract first letter of each word)

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.

  #2   Report Post  
Old January 16th 08, 06:14 PM posted to microsoft.public.excel.worksheet.functions
CLR CLR is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,998
Default Create Acronym (Extract first letter of each word)

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.

  #3   Report Post  
Old January 16th 08, 06:44 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 2,344
Default Create Acronym (Extract first letter of each word)

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.

  #4   Report Post  
Old January 16th 08, 06:48 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 2,344
Default Create Acronym (Extract first letter of each word)

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.

  #5   Report Post  
Old January 16th 08, 06:55 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 2,344
Default Create Acronym (Extract first letter of each word)

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.



  #6   Report Post  
Old January 16th 08, 07:09 PM posted to microsoft.public.excel.worksheet.functions
CLR CLR is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,998
Default Create Acronym (Extract first letter of each word)

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.

  #7   Report Post  
Old January 16th 08, 07:15 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2008
Posts: 4
Default Create Acronym (Extract first letter of each word)

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.

  #8   Report Post  
Old January 16th 08, 07:23 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2008
Posts: 4
Default Create Acronym (Extract first letter of each word)

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.

  #9   Report Post  
Old January 17th 08, 03:44 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2008
Posts: 1
Default Create Acronym (Extract first letter of each word)

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.

  #10   Report Post  
Old January 17th 08, 11:25 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 8,856
Default Create Acronym (Extract first letter of each word)

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 -




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
display one letter of a word in a cell r_chaud Excel Worksheet Functions 2 December 4th 07 09:39 PM
Insert letter/no among word ShamsulZ Excel Discussion (Misc queries) 2 October 5th 06 10:39 AM
Default Capital letter for 1st letter of a word Jeff Excel Discussion (Misc queries) 6 July 10th 06 08:36 AM
How do I count a named range for a specific word or acronym? brandyb Excel Worksheet Functions 1 November 4th 05 07:50 PM
How do I get the first letter of a word in excel Shadowofthedarkgod Excel Discussion (Misc queries) 8 May 31st 05 02:56 AM


All times are GMT +1. The time now is 12:01 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017