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 ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

1 comment:

  1. Greetings! I've been following your blog for a while now and finally got the bravery to go ahead and give you a shout out from Porter Texas!
    Just wanted to say keep up the fantastic job!

    ReplyDelete

Hi User, Thank You for visiting My Blog. Please post your open Feedback only related to this Blog Posts. Please note that I cannot respond to the Anonymous Comments.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts