Extract Email id's with matching domain from list of values

[Deepak Kumar]

Hi all,

Can you help to extract matching domain email addresses from a list of values in a field.

Ex.: MultipleValues in a field of record 1: [email protected], [email protected], [email protected], [email protected], [email protected]

Output :

Want to extract only those email id's whose domain ends with gmail.com.

[email protected], [email protected], [email protected]

 

Thanks in advance.

 

[comment]

Try it this way =

Let ( [
suffix = "gmail.com" ;
list = Substitute ( YourField; ", " ; ¶ ) ;
a = Char (57344) ;
b = Char (57345) & ¶
] ;
FilterValues ( list ; Substitute ( list & ¶ ; [ suffix & ¶ ; a ] ; [ ¶ ; b ] ; [ a ; suffix & ¶ ] ) )
)

The result will be a return-separated list of the matching addresses:

[email protected]
[email protected]
[email protected]

[LaRetta]

I have never seen using Char ( 57344 ) and Char ( 57345 ) before!  So the idea is to use two characters which can not exist in the data as unique beginning and end points and surround the non-gmail.emails with these symbols.  Then replace the beginning (a) with gmail which leaves result below. I used (b) here since forum didn't like the symbol of Char (57345):

[email protected]
[email protected](b)
[email protected]
[email protected](b)
[email protected]

Then those with the (b) at the end would naturally be removed in the FilterValues() since they would no longer exist in the list.  Did I misinterpret anything or miss an important aspect in this principle?

This is so very clever!!  I wish I could rate you UP more than once on it!  What a great way to start my day!  Thank you SO MUCH for sharing your brain with this forum, Michael!!

 

Edited July 16, 2015 by LaRetta

[comment]

So the idea is to use two characters which can not exist in the data as unique beginning and end points and surround the non-gmail.emails with these symbols. 

I would say that the idea is to append something to the values that do not end with the specified suffix. This is done in three steps:

1. Protect the values that do end with the specified suffix from being modified in step 2 by substituting the suffix and the subsequent return with a reserved character (a);
2. Substitute all remaining returns with a reserved character (b) and a return; this effectively appends (b) to all values that do not end with the specified suffix;
3. Reverse step 1 .

The result of this is indeed what you show in your post:

[email protected]
[email protected](b)
[email protected]
[email protected](b)
[email protected]

and when this is used to filter the original list, only the unmodified, wanted values remain.

 

This is so very clever!! 

IIRC, I developed this idea out of something that Agnès Barouh did, I don't recall exactly what that was.

Edited July 16, 2015 by comment

[Steve Martino]

@‌‌ Comment-Comment, please don't ever use your brain for evil    

@LaRetta & Comment-thanks for taking the time to explain.  I could've stared at that for the rest of my life and not understood.  Of course I could have just put it in the Data View and broken it down....

Very nice, gives me an idea.....

[comment]

Of course I could have just put it in the Data View and broken it down....

That's always a good idea.

[Deepak Kumar]

Thanks for sharing idea....