LinkBack URL
About LinkBacks
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
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
Please Login or Register to view this content.
Last edited by JohnTopley; 02-18-2023 at 04:21 PM.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Thank you JT , we leave the thread open for a formula
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:
Then when you want to use it like a formula:Please Login or Register to view this content.
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", "")
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:
Please Login or Register to view this content.
For 3 consecutive vowels, In C1 copied down.
Formula:Please Login or Register to view this content.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
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
You can try:
Its an CSE-entered formula.Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks