Split text.in excel column

Hello Guys,

I am having excel file with acronym list , here i need only three text before hypen

image

my excel column is like above screen shot … i need to change it like below screen shot…

image

Thanks,
Suresh.

1 Like

Hi
we can do this FILTER DATATABLE ACTIVITY
–where use READ RANGE ACTIVITY and get the output with a variable of type datatable named dt
–now use FILTER DATATABLE activity and mention like this
image

where in the column field mention as
0 (if we dont know the columnname) or mention the column name between double quotes
and in the condition mention as CONTAINS
and in value field mention as
"AAB - Average At Best"

finally our datatable variable dt will be having only the rows we want

did that activity helped us on this
Cheers @suresh_kumar4

1 Like

Thanks for your response. i just want to remove the text before “-” .

let say this is my text ‘Average At Best AAB - Average At Best’ i need only the highlighted
part. how can i get that.

please help me on that.

Thanka,
Suresh.

1 Like

awesome
–in that case once after getting the datatable use FOR EACH ROW activity and mention the variable dt as input
–inside the loop use a assign activity like this
row(0) = System.Text.RegularExpressions.Regex.Match(row(0).ToString,"[A-Z]+(?<=AAB).*")

It worked
image

Cheers @suresh_kumar4

Thanks a lot @Palaniyappan,

Here row(0) = “AAB”+System.Text.RegularExpressions.Regex.Match(row(0).ToString,"[A-Z]+(?<=AAB).*")

AAB is not constant it will change let say BTE, SED, FD, GT,…like that will come…in this case the above expression will work???..

Thanks,
Suresh.

1 Like

Thanks a lot @Palaniyappan,

AAB is not constant it will change let say BTE, SED, FD, GT,…like that will come…in this case the above expression will work???..

I Used this expression Test.Substring(Test.IndexOf("—")-4).Trim() i am getting the result outside the loop… when i use it inside the for each loop i am getting error…

Main.xaml (4.9 KB)

Thanks,
Suresh.

1 Like

Fine
in that case we can use SPLIT Method like this
row(0) =String.Join(" “,Split(row(0).ToString,” ").Asenumerable().Skip(3))

here you go with a xaml @suresh_kumar4
hope its resolved
splitt.zip (9.3 KB)

Cheers @suresh_kumar4

Kindly try with the above xaml and let know for any queries or clarification
Cheers @suresh_kumar4

sure … Thanks… i am trying it

1 Like

awesome
Cheers @suresh_kumar4

Hello @Palaniyappan

I tried this row(0) =String.Join(" “,Split(row(0).ToString,” ").Asenumerable().Skip(3))
its working fine outside the loop… when i use it inside the loop its not working…

Thanks,
Suresh.

1 Like

Is don’t get this buddy
Can I have a screenshot of how it was mentioned inside the for each row loop

Cheers @suresh_kumar4

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.