• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Whether a word contains any vowel

A

a_dani20

New Member
hi guys ..Can any formula be made to know that whether a word contains any vowels or not ??? i need a formula which gives true if a word contains any of the 5 vowels otherwise it should give false.
 
This will do it, but there is probably a better way:


=IF(IFERROR(FIND("A",UPPER(C16)),0)+IFERROR(FIND("E",UPPER(C16)),0)+IFERROR(FIND("I",UPPER(C16)),0)+IFERROR(FIND("O",UPPER(C16)),0)+IFERROR(FIND("U",UPPER(C16)),0)>=1,"Vowel","No Vowel")
 
Array formula (Ctrl+Shift+Enter to confirm):

=OR(ISNUMBER(SEARCH({"a","e","i","o","u"},A2)))


If you want more versatility on what to search for, can use range reference as well (assuming words/letters are in the specified range):

=OR(ISNUMBER(SEARCH(B2:B6,A2)))


Mike,

Just as an FYI, you can skip the UPPER functions in your formula if you use SEARCH instead of FIND (search is not case-sensitive)
 
also work without array formula

=SUMPRODUCT(NOT(ISERROR(FIND({"A","E","I","O","U"},UPPER(A1),1)))*1)>0
 
You must log in or register to reply here.
Back
Top