Two consecutive consonants in a name and surname

[Zéev2020]

What formula can I use to identify two consecutive consonants in the name and surname columns.eg name Ndoluvo and surname Khaye formula should identify any two consecutive consonants the name and surname starts with

 

[tyija1995]

[TABLE="class: grid, width: 750"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Consecutive Consonants First Name[/TD]
[TD]Consecutive Consonants Last Name[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Ndoluvo[/TD]
[TD]Khaye[/TD]
[TD]TRUE[/TD]
[TD]TRUE[/TD]
[/TR]
</tbody>[/TABLE]


Using the formula in C2:
=NOT(OR(IFERROR(SEARCH("a",LEFT(A2,2)),FALSE),IFERROR(SEARCH("e",LEFT(A2,2)),FALSE),IFERROR(SEARCH("I",LEFT(A2,2)),FALSE),IFERROR(SEARCH("o",LEFT(A2,2)),FALSE),IFERROR(SEARCH("u",LEFT(A2,2)),FALSE)))

And in D2:
=NOT(OR(IFERROR(SEARCH("a",LEFT(B2,2)),FALSE),IFERROR(SEARCH("e",LEFT(B2,2)),FALSE),IFERROR(SEARCH("I",LEFT(B2,2)),FALSE),IFERROR(SEARCH("o",LEFT(B2,2)),FALSE),IFERROR(SEARCH("u",LEFT(B2,2)),FALSE)))

 

[tyija1995]

Or if you want a more dynamic and shorter formula you can use these in Cells C2 & D2:

C2:
=IF(SUMPRODUCT(--NOT(ISERR(SEARCH({"a";"e";"I";"o";"u"},LEFT(A2,2)))))=0,TRUE,FALSE)

D2:
=IF(SUMPRODUCT(--NOT(ISERR(SEARCH({"a";"e";"I";"o";"u"},LEFT(B2,2)))))=0,TRUE,FALSE)

 

[Zéev2020]

Thank you so much .

 

[DanteAmor]

How about:

=IF(ISNUMBER(SEARCH({"a";"e";"i";"o";"u"},MID(A2,1,2))),"No","Yes")

 

[tyija1995]

How about:

=IF(ISNUMBER(SEARCH({"a";"e";"i";"o";"u"},MID(A2,1,2))),"No","Yes")

Hi Dante, that formula only works if the first 2 letters contain an "a" - just to let you know.

 

[pgc01]

How about:

=IF(ISNUMBER(SEARCH({"a";"e";"i";"o";"u"},MID(A2,1,2))),"No","Yes")

Or if you want a more dynamic and shorter formula you can use these in Cells C2 & D2:
=IF(SUMPRODUCT(--NOT(ISERR(SEARCH({"a";"e";"I";"o";"u"},LEFT(A2,2)))))=0,TRUE,FALSE)

Hi

I tried both formulas for

aabb
bbaa

both have 2 consecutive consonants but I get in both different results for the 2 strings.
Please confirm.

 

[tyija1995]

Hi pgc,

The poster initially stated: "identify any two consecutive consonants the name and surname starts with"

emphasis on the "starts with" hence the LEFT function of the first 2 characters.

It will return false for aabb and true for bbaa because of that logic. It's looking at "aa" and then "bb" respectively. It is true that there are 2 consecutive consonants in both strings, but the criteria of the poster has made my formula ignore any characters after the 2nd.

 

[DanteAmor]

Hi Dante, that formula only works if the first 2 letters contain an "a" - just to let you know.

Thanks tyija1995, I appreciate the comment.

Please try the following:

=IF(SUM(ISNUMBER(SEARCH({"a";"e";"i";"o";"u"},MID(A2,1,2)))+0)>0,"No","Yes")

 

[pgc01]

Hi pgc,

The poster initially stated: "identify any two consecutive consonants the name and surname starts with"

emphasis on the "starts with" hence the LEFT function of the first 2 characters.

It will return false for aabb and true for bbaa because of that logic. It's looking at "aa" and then "bb" respectively. It is true that there are 2 consecutive consonants in both strings, but the criteria of the poster has made my formula ignore any characters after the 2nd.

Thank you.
You are right.

I read "identify any two consecutive consonants the name" and did not read the "starts with".
Will pay more attention next time (I hope )