Formula Help

[volpinex]

Hello,
I need to build a formula which looks up a cell in which there is an email address and if the prefix is E followed by 6 digits and the suffix @gmail.com it returns "TRUE".
For example if in A1 we have E123456@gmail.com the Formula returns true but if it contains E12345k@gmail.com it returns "FALSE".
Thank you for your help.

 

[Toadstool]

Hi Volpinex,

Does this work for you?

Volpinex.xlsx
	A	B
1	Email	Check
2	E123456@gmail.com	TRUE
3	E123456@googlemail.com	FALSE
4	X123456@gmail.com	FALSE
5	E99@gmail.com	TRUE
6	E9XX9@gmail.com	FALSE
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=AND(LEFT(A2,1)="E",ISNUMBER(MID(A2,2,SEARCH("@",A2,2)-2)+0),RIGHT(A2,10)="@Gmail.com")

 

[kweaver]

Welcome to Mr. Excel.

What about

Book1 (version 1).xlsb
	A	B
1	E123456@gmail.com	TRUE
2	Jackson@yahoo.com	FALSE
3	Smith@gmail.com	FALSE
4	X123456@gmail.com	FALSE
5	E777666@gmail.com	TRUE
6	E555@gmail.com	FALSE
Sheet7

Cell Formulas
Range	Formula
B1:B6	B1	=IF(AND("E"=LEFT(A1,1),"gmail.com"=RIGHT(A1,9),ISNUMBER(1*MID(A1,2,6))),"TRUE","FALSE")

 

[JimM]

How about

=IF(AND(LEFT(A2,1)="E",ISNUMBER(--MID(A2,2,6)),RIGHT(A3,10)="@gmail.com"),"TRUE","FALSE")

 

[volpinex]

Hi Volpinex,

Does this work for you?

Volpinex.xlsx
	A	B
1	Email	Check
2	E123456@gmail.com	TRUE
3	E123456@googlemail.com	FALSE
4	X123456@gmail.com	FALSE
5	E99@gmail.com	TRUE
6	E9XX9@gmail.com	FALSE
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=AND(LEFT(A2,1)="E",ISNUMBER(MID(A2,2,SEARCH("@",A2,2)-2)+0),RIGHT(A2,10)="@Gmail.com")

Genius! you are a star! Thank you

 

[volpinex]

How about

=IF(AND(LEFT(A2,1)="E",ISNUMBER(--MID(A2,2,6)),RIGHT(A3,10)="@gmail.com"),"TRUE","FALSE")

Works like a charm too, thank you sooo much!

 

[Toadstool]

Genius! you are a star! Thank you

You're welcome!
Thanks for the feedback.