CODEWITHSUNDEEP
excelvba
jimiclapton
jimiclapton

Reputation: 889

Excel VBA to parse vowels in a string

I have a list of 5char alpha strings made of consonants and vowels. I'm trying to identify the combination in each string.

Eg DBNJE is CCCCV and AHOIS is VCVVC for example.

I've written this as a series of 5 pretty clumsy formulae to assess the C or V property for each of the 5 chars in the string, as below

Char1 

=IF(OR(LEFT($B2,1)="A",LEFT($B2,1)="E",LEFT($B2,1)="I",LEFT($B2,1)="O",LEFT($B2,1)="U"),"V","C")

And

Char2 

=IF(OR(MID($B2,2,1)="A",MID($B2,2,1)="E",MID($B2,2,1)="I",MID($B2,2,1)="O",MID($B2,2,1)="U"),"V","C")

Etc etc

With me so far?

I then concatenate the results in a single cell to give me the combo as in the first example above.

This works fine, but I'd like to automate it using VBA. Could anybody please tell me the best way to go about doing this?

Thanks in advance!

Upvotes: 0

Views: 3000

Answers (5)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60324

Here's a method that makes use of Regular Expressions to define vowels

  • Replace all the vowels with a tilde ~
  • Replace everything that is not a tilde with a C
  • Replace the tilde with a V
Option Explicit
Function VorC(S As String) As String
    Dim RE As Object
    Dim sTemp As String
Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .ignorecase = True
    .Pattern = "[AEIOU]"
End With

sTemp = RE.Replace(S, "~")
RE.Pattern = "[^~]"
sTemp = RE.Replace(sTemp, "C")
VorC = Replace(sTemp, "~", "V")

End Function

If you want to include the occasional Y as a vowel, and are happy with calling it a vowel in the instance where it is surrounded by two consonants, one could change the first Regex Replacement routine to:

With RE
    .Global = True
    .ignorecase = True
    .Pattern = "[AEIOU]|([^AEIOU])Y([^AEIOU])"
End With

sTemp = RE.Replace(S, "$1~$2")

And keep the rest of the UDF unchanged.

However, the rules for calling Y a vowel are more complex than that, and sometimes it's not really a vowel, but part of a dipthong, so things can get fuzzy pretty quickly.

Upvotes: 0

David Zemens
David Zemens

Reputation: 53643

I'll jump on the bandwagon, too... This checks the ASCII code value of each character, ignores anything outside of the A-Z range for consideration.

Public Function GetPattern(ByRef rng As Excel.Range)

Dim str As String ' the value being parsed
Dim ret As String ' the return value for the function
Dim i As Long
Const C As String = "C"
Const V As String = "V"
Dim char As String

str = Trim(UCase(rng.Value))

For i = 1 To Len(str)
    char = Mid(str, i, 1)
    'Ignores numbers and non-printing characters, symbols, etc.
    If 65 <= Asc(char) And Asc(char) <= 90 Then
        Select Case Asc(char)
            Case 65, 69, 73, 79, 85 'Ascii codes for A, E, I, O, U
                ret = ret & V
            Case Else
                ret = ret & C
        End Select
    End If
Next

GetPattern = ret


End Function

Upvotes: 3

basodre
basodre

Reputation: 5770

I created a user defined function to loop through the characters in the string, and return a C if it's a consonant and a V if it's a vowel.

Place the below code in a standard VBA module:

Function ConsonantOrVowel(strWord As String) As String
    Dim nLen As Integer

    nLen = Len(strWord)

    ConsonantOrVowel = ""
    For i = 1 To nLen
        If UCase(Mid(strWord, i, 1)) Like "[AEIOU]" Then
            ConsonantOrVowel = ConsonantOrVowel + "V"
        Else
            ConsonantOrVowel = ConsonantOrVowel + "C"
        End If
    Next i
End Function

Next, in an Excel cell, enter the formula =ConsonantOrVowel(A1) as you would a regular formula. (Note, the preceding example assumes the string you want to test is in cell A1).

Upvotes: 1

Steven
Steven

Reputation: 911

Nothing fancy here. This code would go into a Module and referenced from a cell that would contain the output data.

Public Function ConsonantVowel(ByVal sInputString As String) As String
Dim sReturnData As String
Dim x As Integer

sReturnData = ""
x = 1
While x <= Len(sInputString)

    If UCase(Mid(sInputString, x, 1)) = "A" Or UCase(Mid(sInputString, x, 1)) = "E" Or UCase(Mid(sInputString, x, 1)) = "I" _
        Or UCase(Mid(sInputString, x, 1)) = "O" Or UCase(Mid(sInputString, x, 1)) = "U" Then
        sReturnData = sReturnData & "V"
    Else
        sReturnData = sReturnData & "C"
    End If
    x = x + 1
Wend
ConsonantVowel = sReturnData
End Function

Upvotes: 0

Kyle
Kyle

Reputation: 2545

Insert this function into a module and type "=Words("reference") in a cell where "reference" is a cell containing one of the strings you would like to process. This could also be put into a sub procedure to loop through all your cells of strings, but you would need to supply more information.

Function Words(str as String) as String
Dim str as String
Dim result as string
Dim i as Integer
For i = 1 to Len(str)
If Mid(str,i,1) like [aeiouAEIOU] then
Result = "V" & Result
Else
Result = "C" & Result
End if
Words = Result
End Function

Upvotes: 0

Related Questions