Matching specific email address domains from string

[Hughesy52]

Hi.

We have hundreds of users with at least 8 different proxy addresses/aliases. They cover various email domains, with a mixture of firstname.lastname@domain or PIN@domain.

One example is Joe Bloggs, who has these email addresses associated with this account:
joe.bloggs@aaa.com,joe.bloggs@bbb.aaa.com,joe.bloggs@ccc.com,11111@aaa.com,11111@bbb.aaa.com,11111@ccc.com

Sue Smith may have just a subset of these:
sue.smith@aaa.com,sue.smith@bbb.aaa.com,22222@aaa.com,22222@bbb.aaa.com

How would I check each user against what domains they have associated with their accounts? It has been driving me potty. I could run PowerShell to query each user and have each domain as a NoteProperty and perform a regex search on the proxyAddress, but I thought the powers of Excel would be much quicker.

I am using Office 365.

Thanks in advance,

Hughesy52

 

[lrobbo314]

This seems to do it.

EXCEL
	A	B	C	D	E	F	G
1	proxyAddress	first.last@aaa.com	first.last@bbb.aaa.com	first.last@ccc.com	PIN@aaa.com	PIN@bbb.aaa.com	PIN@ccc.com
2	joe.bloggs@aaa.com,joe.bloggs@bbb.aaa.com,joe.bloggs@ccc.com,11111@aaa.com,11111@bbb.aaa.com,11111@ccc.com	Yes	Yes	Yes	Yes	Yes	Yes
3	sue.smith@aaa.com,sue.smith@bbb.aaa.com,22222@aaa.com,22222@bbb.aaa.com	Yes	Yes	No	Yes	Yes	No
Sheet2

Cell Formulas
Range	Formula
B2:B3	B2	=CHOOSE(OR(First.Last(A2)*SCAN("",TEXTSPLIT(A2,,","),LAMBDA(s,c,TEXTAFTER(c,"@")="aaa.com")))+1,"No","Yes")
C2:C3	C2	=CHOOSE(OR(First.Last(A2)*SCAN("",TEXTSPLIT(A2,,","),LAMBDA(s,c,TEXTAFTER(c,"@")="bbb.aaa.com")))+1,"No","Yes")
D2:D3	D2	=CHOOSE(OR(First.Last(A2)*SCAN("",TEXTSPLIT(A2,,","),LAMBDA(s,c,TEXTAFTER(c,"@")="ccc.com")))+1,"No","Yes")
E2:E3	E2	=CHOOSE(OR(SCAN("",TEXTSPLIT(A2,,","),LAMBDA(s,c,ISNUMBER(TEXTBEFORE(c,"@")+0)))*SCAN("",TEXTSPLIT(A2,,","),LAMBDA(s,c,TEXTAFTER(c,"@")="aaa.com")))+1,"No","Yes")
F2:F3	F2	=CHOOSE(OR(SCAN("",TEXTSPLIT(A2,,","),LAMBDA(s,c,ISNUMBER(TEXTBEFORE(c,"@")+0)))*SCAN("",TEXTSPLIT(A2,,","),LAMBDA(s,c,TEXTAFTER(c,"@")="bbb.aaa.com")))+1,"No","Yes")
G2:G3	G2	=CHOOSE(OR(SCAN("",TEXTSPLIT(A2,,","),LAMBDA(s,c,ISNUMBER(TEXTBEFORE(c,"@")+0)))*SCAN("",TEXTSPLIT(A2,,","),LAMBDA(s,c,TEXTAFTER(c,"@")="ccc.com")))+1,"No","Yes")

Here is the code for the named Lambda 'First.Last'.

=LAMBDA(txt,SCAN("",TEXTSPLIT(txt,,","),LAMBDA(s,c,TEXTJOIN(".",,SCAN("",TEXTSPLIT(TEXTBEFORE(c,"@"),"."),LAMBDA(s,c,"#")))))="#.#")

 

[Hughesy52]

This seems to do it.

EXCEL
	A	B	C	D	E	F	G
1	proxyAddress	first.last@aaa.com	first.last@bbb.aaa.com	first.last@ccc.com	PIN@aaa.com	PIN@bbb.aaa.com	PIN@ccc.com
2	joe.bloggs@aaa.com,joe.bloggs@bbb.aaa.com,joe.bloggs@ccc.com,11111@aaa.com,11111@bbb.aaa.com,11111@ccc.com	Yes	Yes	Yes	Yes	Yes	Yes
3	sue.smith@aaa.com,sue.smith@bbb.aaa.com,22222@aaa.com,22222@bbb.aaa.com	Yes	Yes	No	Yes	Yes	No
Sheet2

Cell Formulas
Range	Formula
B2:B3	B2	=CHOOSE(OR(First.Last(A2)*SCAN("",TEXTSPLIT(A2,,","),LAMBDA(s,c,TEXTAFTER(c,"@")="aaa.com")))+1,"No","Yes")
C2:C3	C2	=CHOOSE(OR(First.Last(A2)*SCAN("",TEXTSPLIT(A2,,","),LAMBDA(s,c,TEXTAFTER(c,"@")="bbb.aaa.com")))+1,"No","Yes")
D2:D3	D2	=CHOOSE(OR(First.Last(A2)*SCAN("",TEXTSPLIT(A2,,","),LAMBDA(s,c,TEXTAFTER(c,"@")="ccc.com")))+1,"No","Yes")
E2:E3	E2	=CHOOSE(OR(SCAN("",TEXTSPLIT(A2,,","),LAMBDA(s,c,ISNUMBER(TEXTBEFORE(c,"@")+0)))*SCAN("",TEXTSPLIT(A2,,","),LAMBDA(s,c,TEXTAFTER(c,"@")="aaa.com")))+1,"No","Yes")
F2:F3	F2	=CHOOSE(OR(SCAN("",TEXTSPLIT(A2,,","),LAMBDA(s,c,ISNUMBER(TEXTBEFORE(c,"@")+0)))*SCAN("",TEXTSPLIT(A2,,","),LAMBDA(s,c,TEXTAFTER(c,"@")="bbb.aaa.com")))+1,"No","Yes")
G2:G3	G2	=CHOOSE(OR(SCAN("",TEXTSPLIT(A2,,","),LAMBDA(s,c,ISNUMBER(TEXTBEFORE(c,"@")+0)))*SCAN("",TEXTSPLIT(A2,,","),LAMBDA(s,c,TEXTAFTER(c,"@")="ccc.com")))+1,"No","Yes")

Here is the code for the named Lambda 'First.Last'.

=LAMBDA(txt,SCAN("",TEXTSPLIT(txt,,","),LAMBDA(s,c,TEXTJOIN(".",,SCAN("",TEXTSPLIT(TEXTBEFORE(c,"@"),"."),LAMBDA(s,c,"#")))))="#.#")

Hi Irobbo314. Thanks so much for your reply. I shall give it a go in a short while.

 

[Hughesy52]

This seems to do it.

EXCEL
	A	B	C	D	E	F	G
1	proxyAddress	first.last@aaa.com	first.last@bbb.aaa.com	first.last@ccc.com	PIN@aaa.com	PIN@bbb.aaa.com	PIN@ccc.com
2	joe.bloggs@aaa.com,joe.bloggs@bbb.aaa.com,joe.bloggs@ccc.com,11111@aaa.com,11111@bbb.aaa.com,11111@ccc.com	Yes	Yes	Yes	Yes	Yes	Yes
3	sue.smith@aaa.com,sue.smith@bbb.aaa.com,22222@aaa.com,22222@bbb.aaa.com	Yes	Yes	No	Yes	Yes	No
Sheet2

Cell Formulas
Range	Formula
B2:B3	B2	=CHOOSE(OR(First.Last(A2)*SCAN("",TEXTSPLIT(A2,,","),LAMBDA(s,c,TEXTAFTER(c,"@")="aaa.com")))+1,"No","Yes")
C2:C3	C2	=CHOOSE(OR(First.Last(A2)*SCAN("",TEXTSPLIT(A2,,","),LAMBDA(s,c,TEXTAFTER(c,"@")="bbb.aaa.com")))+1,"No","Yes")
D2:D3	D2	=CHOOSE(OR(First.Last(A2)*SCAN("",TEXTSPLIT(A2,,","),LAMBDA(s,c,TEXTAFTER(c,"@")="ccc.com")))+1,"No","Yes")
E2:E3	E2	=CHOOSE(OR(SCAN("",TEXTSPLIT(A2,,","),LAMBDA(s,c,ISNUMBER(TEXTBEFORE(c,"@")+0)))*SCAN("",TEXTSPLIT(A2,,","),LAMBDA(s,c,TEXTAFTER(c,"@")="aaa.com")))+1,"No","Yes")
F2:F3	F2	=CHOOSE(OR(SCAN("",TEXTSPLIT(A2,,","),LAMBDA(s,c,ISNUMBER(TEXTBEFORE(c,"@")+0)))*SCAN("",TEXTSPLIT(A2,,","),LAMBDA(s,c,TEXTAFTER(c,"@")="bbb.aaa.com")))+1,"No","Yes")
G2:G3	G2	=CHOOSE(OR(SCAN("",TEXTSPLIT(A2,,","),LAMBDA(s,c,ISNUMBER(TEXTBEFORE(c,"@")+0)))*SCAN("",TEXTSPLIT(A2,,","),LAMBDA(s,c,TEXTAFTER(c,"@")="ccc.com")))+1,"No","Yes")

Here is the code for the named Lambda 'First.Last'.

=LAMBDA(txt,SCAN("",TEXTSPLIT(txt,,","),LAMBDA(s,c,TEXTJOIN(".",,SCAN("",TEXTSPLIT(TEXTBEFORE(c,"@"),"."),LAMBDA(s,c,"#")))))="#.#")

Hi Irobbo314. The formulae for E, F & G work perfectly, thank you. I have tried the formula for cell B, C & D and I seem to be getting a #NAME error near the end.

Any ideas?

 

[lrobbo314]

You need to copy and paste that formula I posted at the bottom as a defined name. Like if you were naming a range. Then give it the name First.Last

 

[Hughesy52]

Ah ok I see, thank you. I think I'm a bit scuppered here. I've been editing the spreadsheet online and it will accept the formulas, but I can't define names. And when I edit the spreadsheet locally and attempt to add the formulas, I keep running into this error because I don't think it understand one of them.

I think I've got an older version of Excel on my laptop, so I'll try and get it updated.

Thank you for all your help on this.