Split cells in excel

Help
1

Hi,

I have excel with 2000 rows and 3 columns. I want to split the column 3 cell and paste into column 1 and column 2. I have used datatable but it is taking more time to compute. Is there any formula to use in write cell activity.

output:
A B C
123 India 123_India
456 China 456_China
789 Japan 789_Japan
.
.
.
.
.
. 2000 rows

Regards
Sailaja

2 Likes
2

hope this steps could help you resolve this
–use a excel application scope and pass the file path as input
–use a read range activity and get the output with a variable of type dattable named outdt
–use a for each row loop and pass the above variable as input
–inside the loop use a assign activity like this
out_value1 = Split(row(“yourcolumnname”).ToString,“_”)(0).ToString
and another assign activity like this
out_value2 = Split(row(“yourcolumnname”).ToString,“_”)(1).ToString
and now use assign activity like this
row(“yourcolumn1name”) = out_value1
and another activity with
row(“yourcolumn2name”) = out_value2

hope this would help you
Cheers @Sailaja_Pasupuleti

3 Likes
3

I have tried the above solution but I am having more number of rows which is taking time to loop through. Any other solution ?

4

@Sailaja_Pasupuleti The solution given by @Palaniyappan should work in less than a second.

You should be reading the entire workflow into a datatable. Editing columns 1 + 2 information, then pasting the datatable into excel. If it is 2000 rows the portion working through the datatable should be done in less than a second. Can you upload the XML if it is taking longer than that? I’d be happy to take a look

5

No worries
i hope that would work as expected and i dont find any method apart from this
may be there could be
but this comes first to my mind, kindly share your xaml if possible so that we could check on its performance.
Cheers @Sailaja_Pasupuleti

6

I have used this formula in write cell activity “=RIGHT(C2, LEN(C2)-FIND(”“_”“,C2))” and in the range “A2:A”+dt1.Rows.Count.ToString. This worked out for me. Thanks all for the response.

1 Like
7

I hope again that could be passed in Loop only, which is same as split method
Cheers @Dave

8

Hi, same scenario im scrapping data from web page and i have to split cell value and final result i want to store in excel …thanks in advance