I'm trying to create 2 character alpha codes from whole words. The rules I
need to apply require me to use the first letter from the word followed by
the next consonant. I can seperate the first letter easily but I have no idea
how to capture the following consonant.

On Sun, 12 Jul 2009 00:06:01 -0700, ozmann08
wrote:

I'm trying to create 2 character alpha codes from whole words. The rules I
need to apply require me to use the first letter from the word followed by
the next consonant. I can seperate the first letter easily but I have no idea
how to capture the following consonant.

You can do this easily with a User Defined Function.

To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=SpecCode(A1)

in some cell.

The Code returned is upper case, and the value returned is the first Character,
and the next character that is not a vowel. I assumed all of your words
consisted of "letters" and that there were no digits, hyphens, underscores, etc
to deal with. If that is not the case, the comparison can be expanded.

==================================
Option Explicit
Option Compare Text
Function SpecCode(str As String) As String
Dim sTemp As String
Dim i As Long
For i = 2 To Len(str)
sTemp = Mid(str, i, 1)
If Not sTemp Like "[aeiouy]" Then
SpecCode = UCase(Left(str, 1) & sTemp)
Exit Function
End If
Next i
SpecCode = "No Pattern Match"
End Function
=================================
--ron

I'm trying to create 2 character alpha codes from whole words. The rules I
need to apply require me to use the first letter from the word followed by
the next consonant. I can seperate the first letter easily but I have no
idea
how to capture the following consonant.

You can do this easily with a User Defined Function.

To enter this User Defined Function (UDF), <alt-F11 opens the Visual
Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=SpecCode(A1)

in some cell.

The Code returned is upper case, and the value returned is the first
Character,
and the next character that is not a vowel. I assumed all of your words
consisted of "letters" and that there were no digits, hyphens,
underscores, etc
to deal with. If that is not the case, the comparison can be expanded.

==================================
Option Explicit
Option Compare Text
Function SpecCode(str As String) As String
Dim sTemp As String
Dim i As Long
For i = 2 To Len(str)
sTemp = Mid(str, i, 1)
If Not sTemp Like "[aeiouy]" Then
SpecCode = UCase(Left(str, 1) & sTemp)
Exit Function
End If
Next i
SpecCode = "No Pattern Match"
End Function
=================================

Instead of using Option Compare Text to make your Like comparison case
insensitive, you could remove it and use this Like comparison instead...

If Not sTemp Like "[AEIOUYaeiouy]" Then

and instead of using the Not keyword, I would probably have used the Like
operator's negation meta-character (which is the exclamation mark)...

If sTemp Like "[!AEIOUYaeiouy]" Then

Of course, another way to do this would be to remove the Option Compare Text
statement and use this statement to assign the values to sTemp...

sTemp = LCase(Mid(str, i, 1))

Then you can leave the rest of your code exactly as you posted it and it
will work correctly.

--
Rick (MVP - Excel)

On Sun, 12 Jul 2009 12:03:23 -0400, "Rick Rothstein"
wrote:

Instead of using Option Compare Text to make your Like comparison case
insensitive, you could remove it and use this Like comparison instead...

If Not sTemp Like "[AEIOUYaeiouy]" Then

and instead of using the Not keyword, I would probably have used the Like
operator's negation meta-character (which is the exclamation mark)...

If sTemp Like "[!AEIOUYaeiouy]" Then

Of course, another way to do this would be to remove the Option Compare Text
statement and use this statement to assign the values to sTemp...

sTemp = LCase(Mid(str, i, 1))

Then you can leave the rest of your code exactly as you posted it and it
will work correctly.

Yes, there are many different ways to solve this problem. Some even include
the Regular Expressions you seem to dislike -- but I posted one subsequently
:-)
--ron

Instead of using Option Compare Text to make your Like comparison case
insensitive, you could remove it and use this Like comparison instead...

If Not sTemp Like "[AEIOUYaeiouy]" Then

and instead of using the Not keyword, I would probably have used the Like
operator's negation meta-character (which is the exclamation mark)...

If sTemp Like "[!AEIOUYaeiouy]" Then

Of course, another way to do this would be to remove the Option Compare
Text
statement and use this statement to assign the values to sTemp...

sTemp = LCase(Mid(str, i, 1))

Then you can leave the rest of your code exactly as you posted it and it
will work correctly.

Yes, there are many different ways to solve this problem.

Yes, I know you knew that... I just thought the readers of this thread might
find it interesting to see some of them.

Some even include the Regular Expressions you seem to
dislike -- but I posted one subsequently :-)

I don't dislike Regular Expressions (really, I don't), it is just that I
feel most people posting questions here are not all that familiar with them
and would be more comfortable with solutions coded using native VB
functions, operators, etc. I do feel your posting Regular Expression
solutions is important, though, both for those reading this thread now, and
those reading the thread later on in the archives, who are familiar with
them.

--
Rick (MVP - Excel)

On Sun, 12 Jul 2009 14:21:47 -0400, "Rick Rothstein"
wrote:

I don't dislike Regular Expressions (really, I don't), it is just that I
feel most people posting questions here are not all that familiar with them
and would be more comfortable with solutions coded using native VB
functions, operators, etc. I do feel your posting Regular Expression
solutions is important, though, both for those reading this thread now, and
those reading the thread later on in the archives, who are familiar with
them.

Well, I'll continue. But *I* have to remember that sometimes a native solution
is better. However, it's usually so much quicker to develop the solution using
Regex...

Speaking of native VB -- a question you might be able to answer. It has
nothing to do with Excel other than the solution is VBA.

I have been using Collections to detect duplicates. This seems like a simple
method because if you try to add a member with an already used key, an error
occurs.

I have a situation where I am getting duplicate notifications (from a gov't
website) of a particular event. Usually one original and three duplicates. I
have been fooling around with VBA and Outlook in an attempt to programmatically
remove the duplicates. The definitive property defining to me that this is a
duplicate is the message body. The message id's are different so I can't use
that.

Question: Is there a limit to the size of the key in a Collection that might
affect using this method?
--ron

On Sun, 12 Jul 2009 00:06:01 -0700, ozmann08
wrote:

I'm trying to create 2 character alpha codes from whole words. The rules I
need to apply require me to use the first letter from the word followed by
the next consonant. I can seperate the first letter easily but I have no idea
how to capture the following consonant.

If you want to use Regular Expressions to accomplish the same task, then the
following should also work. It was tweaked so it would ONLY recognize letters
as valid characters:

==============================
Option Explicit
Function SpecCode(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.IgnoreCase = True
re.Global = False
re.Pattern = "^([a-z]).*?([bcdfghjklmnpqrstvwxz]).*"
If re.test(str) = True Then
SpecCode = UCase(re.Replace(str, "$1$2"))
Else
SpecCode = "No Pattern Match"
End If
End Function
==============================

References regarding Regular Expressions:

Regular Expressions
http://www.regular-expressions.info/reference.html
http://support.microsoft.com/default...02&Product=vbb
http://msdn2.microsoft.com/en-us/library/6wzad2b2.aspx
http://msdn2.microsoft.com/en-us/library/ms974619.aspx
http://www.regex-guru.info/
--ron

One way
Sub getconstanant()
For Each c In Range("f1:f5")
'On Error Resume Next
For i = 2 To Len(c)
mc = LCase(Mid(c, i, 1))
If Not IsNumeric(mc) And _
mc < "a" And _
mc < "e" And _
mc < "i" And _
mc < "o" And _
mc < "u" Then
Exit For
End If
Next i
MsgBox Left(c, 1) & mc
Next c
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ozmann08" wrote in message
...
I'm trying to create 2 character alpha codes from whole words. The rules I
need to apply require me to use the first letter from the word followed by
the next consonant. I can seperate the first letter easily but I have no
idea
how to capture the following consonant.

On Sun, 12 Jul 2009 07:29:30 -0500, "Don Guillett"
wrote:

One way
Sub getconstanant()
For Each c In Range("f1:f5")
'On Error Resume Next
For i = 2 To Len(c)
mc = LCase(Mid(c, i, 1))
If Not IsNumeric(mc) And _
mc < "a" And _
mc < "e" And _
mc < "i" And _
mc < "o" And _
mc < "u" Then
Exit For
End If
Next i
MsgBox Left(c, 1) & mc
Next c
End Sub

Don,

Isn't "y" usually a vowel when it is not the first letter of the word?
--ron

Probably!. Not an English major

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ron Rosenfeld" wrote in message
...
On Sun, 12 Jul 2009 07:29:30 -0500, "Don Guillett"

wrote:

One way
Sub getconstanant()
For Each c In Range("f1:f5")
'On Error Resume Next
For i = 2 To Len(c)
mc = LCase(Mid(c, i, 1))
If Not IsNumeric(mc) And _
mc < "a" And _
mc < "e" And _
mc < "i" And _
mc < "o" And _
mc < "u" Then
Exit For
End If
Next i
MsgBox Left(c, 1) & mc
Next c
End Sub

Don,

Isn't "y" usually a vowel when it is not the first letter of the word?
--ron

http://www.askoxford.com/asktheexper...tother/lettery
BTW, I was trying to remember how to properly use LIKE
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ron Rosenfeld" wrote in message
...
On Sun, 12 Jul 2009 07:29:30 -0500, "Don Guillett"

wrote:

One way
Sub getconstanant()
For Each c In Range("f1:f5")
'On Error Resume Next
For i = 2 To Len(c)
mc = LCase(Mid(c, i, 1))
If Not IsNumeric(mc) And _
mc < "a" And _
mc < "e" And _
mc < "i" And _
mc < "o" And _
mc < "u" Then
Exit For
End If
Next i
MsgBox Left(c, 1) & mc
Next c
End Sub

Don,

Isn't "y" usually a vowel when it is not the first letter of the word?
--ron

On Sun, 12 Jul 2009 08:07:35 -0500, "Don Guillett"
wrote:

http://www.askoxford.com/asktheexper...tother/lettery

Yes that really clears it up :-(

I guess the OP will have to decide how to treat it.

BTW, I was trying to remember how to properly use LIKE
--ron