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.
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
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
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.
@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)
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:
This is how the pattern works irrespective of case sensitivity:
And, the output of this actions will be:
Hope this helps,
Best Regards.
Palaniyappan
Nguyen_Van_Luong1
Anil_G
mpraveen1902
Yoichi
ppr
Shiva_Nikhil
mkankatala
supermanPunch
anjasing