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.

 

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]

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, 201510 yr by LaRetta

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, 201510 yr by comment

@‌‌ 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.....

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

That's always a good idea.

Thanks for sharing idea....