Monday, 9 March 2015

How to Find the Count of Vowels and Consonants in a String in Excel VBA Macro

Excel VBA Macro to Find the Count of Vowels and Consonants in a String
Suppose we have a String " ExcEl kIngdOm fOr ExcEl vbA mAcrOs" as shown below. From this we can find the Count of Vowels and Consonants as follows :



Excel Formula to Count Consonants :

 =LEN(A1)-SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),{"a","e","i","o","u"},"" )))

Excel Formula to Count Vowels:

 =SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),{"a","e","i","o","u"},"" )))

--------------------------------------------------------------------------------------------------
VBA Macro to Count Consonants :

Sub Consonent_Count()
Dim Cons_Count As Integer
Dim Str

Dim KCount As Integer
Dim i As Integer
Dim Chr As String

Str = ActiveSheet.Range("A1").Value
KCount = 0

For i = 1 To Len(Str)

Chr = UCase(Mid(Str, i, 1))

If Not Chr Like "[AEIOU]" Then
KCount = KCount + 1
End If

Next i

Cons_Count = KCount

MsgBox Cons_Count

End Sub
--------------------------------------------------------------------------------------------------
VBA Macro to Count Vowels:

Sub VowelsCount()
Dim Vowel_Count As Integer
Dim Str

Dim KCount As Integer
Dim i As Integer
Dim Chr As String

Str = ActiveSheet.Range("A1").Value
KCount = 0

For i = 1 To Len(Str)

Chr = UCase(Mid(Str, i, 1))

If Chr Like "[AEIOU]" Then
KCount = KCount + 1
End If

Next i

Vowel_Count = KCount

MsgBox Vowel_Count

End Sub

Note :
Hi My dear users you can use the above technique for your own requirement instead of Counting of Vowels and Consonants.

Thanks,
TAMATAM
        [ BI-Reporting Analyst ]

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts