I want to extract only @gmail.com and want to remove entire data. Can anyone help me please. It's Urgent


In the provided the screenshot i will get the data in that format but i should extract only @gmail.com mail id’s and rest of them i should delete can anyone help me please.

Hi @sandhyareddy

Use matches activity
Gv regex expression as(?<=gmail.com).*

Thanks
Ashwin.S

How can i remove the other data.

Is the email address always the last item in the sentence? If so you could split the string by / and just get the last item from the list.

Strings.Split("a/b/c/d/something1@gmail.com", "/").Last

image

Result.xlsx (22.0 KB)
this is the excel file which i should filter in this i should keep only @gmail.com mail id’s and rest of them should be removed and keep row as blank

There’s a lot of “ESC@gmail”, do you want those?

Hi @sandhyareddy - Please check the attached workflow

SampleWorkflow.zip (10.3 KB)

Input

Input

Output

Output

To clear the contents in an excel, I’ve used VBA, to run this code make sure you do the below settings in your excel.

Open excel > Options > Trust Center > Trust Center settings > Macro Settings > Enable Trust access to VAB object project model

Yes if the mail id ends with @gmail.com i want that and other it should be removed and leave as blank for that row.

SampleWorkflow.zip (10.3 KB)
Can you plz check this once i am getting an error. like this

@sandhyareddy, Download this file to your downloads folder, unzip, copy to your documents folder, try to open it from there and check how that goes.

already has done that. Still getting same error

@sandhyareddy, The logic built to extract the email ids with @gmail.com, if there were no mail ids matches, then the assign activity throws an exception (Source contains no data rows). You can keep this activity within Try Catch block, if exception that means there is no data matches, else some data present.

Let us know if you want to extract the data with only @gmail.com or with any other matches like @gmail.COM etc. if possible share your input excel

I should compare with @UK.GMAIL.COM, Here i got the data but not in which i wanted form. please find attached excel as input and i want data in Column B format. That means if @uk.gmail.com is not matched that should be removed and leave as blank as shown on column B
Result.xlsx (22.9 KB)

Hi @sandhyareddy

You can use the following query to extract the data that contains gmail.com (Irrespective whether it’s Gmail.Com or GMAIL.COM or gmail.COM):

io_dt.AsEnumerable.ToList.ForEach(Sub(row)
row(1) = If(System.Text.RegularExpressions.Regex.IsMatch(row("EmailAddress").ToString,"\b[A-Za-z0-9._%+-]+@.*[Gg][Mm][Aa][Ii][Ll]\.[Cc][Oo][Mm]\b").ToString,row("EmailAddress").ToString,String.Empty)
End Sub)

Parameters for the Invoke Code will be:

This RegEx expression will determine the presence of the desired patter & returns a true/false value:

image

This is how the pattern works irrespective of case sensitivity:

image

And, the output of this actions will be:

image

Hope this helps,
Best Regards.