Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old January 17th 08, 06: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)

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 -




  #12   Report Post  
Old January 17th 08, 07:46 PM 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)

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 -


  #13   Report Post  
Old January 19th 08, 09:00 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 5,651
Default Create Acronym (Extract first letter of each word)

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
  #14   Report Post  
Old January 22nd 08, 03:40 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 5,651
Default Create Acronym (Extract first letter of each word)

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
  #15   Report Post  
Old April 24th 14, 11:34 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2014
Posts: 1
Default Create Acronym (Extract first letter of each word)

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


  #16   Report Post  
Old June 5th 19, 04:48 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2019
Posts: 1
Default Create Acronym (Extract first letter of each word)

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?
  #17   Report Post  
Old June 5th 19, 05:13 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2019
Posts: 5
Default Create Acronym (Extract first letter of each word)

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


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:51 PM.

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