mark Y if word has 4 consecutive vowels

**makinmomb** · 02-18-2023, 02:48 PM

I have this on a1

RAOULIA
SAOUARI
DIANOIA
URAEMIA

I need formula on b to mark Y on SAOUARI

then i have question 2

I have this on a1

RAOULIA
SAOUARI
DIANOIA
URAEMIA

I need formula on b to mark Y on RAOULIA and DIANOIA since it has 3 vowels in a row

**JohnTopley** · 02-18-2023, 03:48 PM

Sub test()
Dim a

a = [A1].CurrentRegion
ReDim b(1 To UBound(a, 1), 1 To 2)

For r = 1 To UBound(a, 1)
    nv = 0: n = 0
    For i = 1 To Len(a(r, 1)) - 3
        If Mid(a(r, 1), i, 1) Like "*[AEIOU]*" And Mid(a(r, 1), i + 1, 1) Like "*[AEIOU]*" _
        And Mid(a(r, 1), i + 2, 1) Like "*[AEIOU]*" And Mid(a(r, 1), i + 3, 1) Like "*[AEIOU]*" Then
            b(r, 1) = "Y"
            Exit For
        End If
     Next i
     If b(r, 1) = "Y" Then GoTo nextr
     For i = 1 To Len(a(r, 1)) - 2
        If Mid(a(r, 1), i, 1) Like "*[AEIOU]*" And Mid(a(r, 1), i + 1, 1) Like "*[AEIOU]*" _
        And Mid(a(r, 1), i + 2, 1) Like "*[AEIOU]*" Then
                b(r, 2) = "Y"
                Exit For
         End If
    Next i
nextr:
Next r
[B1].Resize(UBound(b, 1), 2) = b
End Sub

**makinmomb** · 02-18-2023, 10:31 PM

Thank you JT , we leave the thread open for a formula

**johnnyL** · 02-19-2023, 12:24 AM

I came up with a UDF (UserDefinedFunction) to allow you to use a formula on the sheet that uses the UDF to complete the result of the formula. Just copy the UDF to a module in the workbook and then You will be able to use it like a formula.

The UDF to copy to a module:

Function FCV(CellAddress As String, ConsecutiveVowelCount As Long) As Boolean           ' FCV stands for Find Consecutive Vowels
    Dim StringPosition          As Long
    Dim CVC                     As Long                                                 ' CVC stands for Consecutive Vowel Count
'
    For StringPosition = 1 To Len(CellAddress)                                          ' Loop through characters of CellAddress
        If LCase(Mid(CellAddress, StringPosition, 1)) Like "[aeiou]" Then               '   If a vowel is found then ...
            CVC = CVC + 1                                                               '       Increment CVC
'
            If CVC = ConsecutiveVowelCount Then                                         '       If we have reached desired CVC then ...
                FCV = True                                                              '           Set FCV = True
                Exit Function                                                           '           Exit this Function
            End If
        Else                                                                            '       Else ...
            CVC = 0                                                                     '           Reset CVC
        End If
    Next                                                                                ' Loop back
'
    FCV = False                                                                         ' Set FCV to False
End Function

Then when you want to use it like a formula:
just enter a formula in the worksheet something like:

=IF(FCV(A1,3), "Y", "")

A1 is the address to check
3 is the # of consecutive vowel values to check for
Y is the result you want to display if # of consecutive vowel values were found
"" is the result if # of consecutive vowel values were not found

Example formulas to put in cell on sheet:

=IF(FCV(A1,3), "Y", "")

Or

=IF(FCV(A2,4), "Y", "")

**kvsrinivasamurthy** · 02-19-2023, 01:16 AM

Formula solution. TEXTJOIN function is used. But 2010 version is not having TEXTJOIN function. It may help somebody.
For 4 consecutive vowels, In B1 copied down.

Formula:

=IF(ISNUMBER(FIND("1111",TEXTJOIN("",TRUE,1*(ISNUMBER(FIND(MID(A1,ROW($1:$7),1),"AEIOU")))))),"Y","")

For 3 consecutive vowels, In C1 copied down.

Formula:

=IF(ISNUMBER(FIND("111",TEXTJOIN("",TRUE,1*(ISNUMBER(FIND(MID(A1,ROW($1:$7),1),"AEIOU")))))),"Y","")

**windknife** · 02-20-2023, 05:54 AM

Try this,

B1
=IF(SUM(--(FREQUENCY(IF(ISNUMBER(FIND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"AEIOU")),ROW(INDIRECT("1:"&LEN(A1)))),IF(NOT(ISNUMBER(FIND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"AEIOU"))),ROW(INDIRECT("1:"&LEN(A1)))))=4)),"Y","")

committed with Ctrl+Shift+Enter, copied down

C1
=IF(SUM(--(FREQUENCY(IF(ISNUMBER(FIND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"AEIOU")),ROW(INDIRECT("1:"&LEN(A1)))),IF(NOT(ISNUMBER(FIND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"AEIOU"))),ROW(INDIRECT("1:"&LEN(A1)))))=3)),"Y","")

committed with Ctrl+Shift+Enter, copied down

**JvdV** · 02-20-2023, 08:22 AM

You can try:

=IF(MAX(FREQUENCY(IF(ISNUMBER(SEARCH(MID(A1,ROW(A$1:INDEX(A:A,LEN(A1))),1),"aeiou")),ROW(A$1:INDEX(A:A,LEN(A1)))),IF(ISNUMBER(SEARCH(MID(A1,ROW(A$1:INDEX(A:A,LEN(A1))),1),"aeiou")),0,ROW(A$1:INDEX(A:A,LEN(A1))))))=3,"Y","N")

Its an CSE-entered formula.

mark Y if word has 4 consecutive vowels

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

mark Y if word has 4 consecutive vowels

Re: mark Y if word has 4 consecutive vowels

Re: mark Y if word has 4 consecutive vowels

Re: mark Y if word has 4 consecutive vowels

Re: mark Y if word has 4 consecutive vowels

Re: mark Y if word has 4 consecutive vowels

Re: mark Y if word has 4 consecutive vowels

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] If last two letters vowels MARK as Y

[SOLVED] Mark Y if last two letters are VOWELS

[SOLVED] How to remove all vowels from a group of words unless it is the first letter of a word?

if last word is S , comment MARK

[SOLVED] Any three consecutive vowels summary

[SOLVED] to separate the vowels and the consonants of a word.

mark data repeated in consecutive cell in a column

Bookmarks

Bookmarks

Posting Permissions