How do I copy first consonant from a word into new cell

[ozmann08]

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.

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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.aspx?scid=kb;en-us;818802&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

 

[Don Guillett]

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

 

[Ron Rosenfeld]

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

 

[Don Guillett]

Probably!. Not an English major

 

[Don Guillett]

http://www.askoxford.com/asktheexperts/faq/aboutother/lettery
BTW, I was trying to remember how to properly use LIKE

 

[Ron Rosenfeld]

http://www.askoxford.com/asktheexperts/faq/aboutother/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

 

[Rick Rothstein]

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.

 

[Ron Rosenfeld]

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

 

[Rick Rothstein]

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.

 

[Ron Rosenfeld]

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

 

[Rick Rothstein]

Question: Is there a limit to the size of the key in a Collection

that might affect using this method?

I've never seen anything published about it, but the following code seems to
indicate the limit is probably more than will have significance to you...

Sub TestCollectionKeyLimitSize()
Dim C As New Collection
C.Add "A", String(1000000, "X")
C.Add "B", String(1000000, "Y")
C.Add "C", String(1000000, "Z")
C.Add "D", String(1000000, "X")
End Sub

Here I am adding three elements to the collection named C using a key of one
million characters each (X's, Y's and Z's). When I run the code, the
debugger stops on the last item saying it is a duplicate key, so it appears
to have accepted the one million characters long keys and is apparently
using them correctly.

 

[Ron Rosenfeld]

I've never seen anything published about it, but the following code seems to
indicate the limit is probably more than will have significance to you...

Sub TestCollectionKeyLimitSize()
Dim C As New Collection
C.Add "A", String(1000000, "X")
C.Add "B", String(1000000, "Y")
C.Add "C", String(1000000, "Z")
C.Add "D", String(1000000, "X")
End Sub

Here I am adding three elements to the collection named C using a key of one
million characters each (X's, Y's and Z's). When I run the code, the
debugger stops on the last item saying it is a duplicate key, so it appears
to have accepted the one million characters long keys and is apparently
using them correctly.

Thanks for that information, Rick.
--ron