How can I count the vowels of a word in a cell in Microsoft Excel?

Follow-up Question. What is the formula so that all the letters in Microsoft Excel are in all caps?

How can I count the vowels of a word in a cell in Microsoft Excel?
About Q1


Use this formula to count the number of a letter in a cell


=LEN( B5)-LEN( SUBSTITUTE( B5, "e",""))


This will search for the letter "e", so you may have somthing like this


=LEN( B5)-LEN( SUBSTITUTE( B5, "e",""))+ =LEN( B5)-LEN( SUBSTITUTE( B5, "a","")) + =LEN( B5)-LEN( SUBSTITUTE( B5, "i",""))


and so on





About Q2


use the function UPPER, like this


=UPPER( A1)





Thats it





Trust me, I am the VBAXLMan
Reply:'write this function





Function IsVowel(letter As String) As Boolean


vowelList = "aeiou"


IsVowel = False


If InStr(vowelList, LCase(letter)) %26gt; 0 Then IsVowel = True


End Function





'then to count the vowels





word = Sheet1.Cells(1, 1) ' or whatever cell you need


nr = 0


For i = 1 To Len(word)


If IsVowel(Mid(word, i, 1)) Then nr = nr + 1


Next


MsgBox "Number of vowels: " + Str(nr)





' write letters in caps


Sheet1.Cells(1, 1) = UCase(Sheet1.Cells(1, 1))