Excel file column separation

In this image, under “location” both city and state exists. I have to separate them as “city” and “State” column.
How will I do it??

Hi @nirmalya.sarkar

Please Build datatable activity to add a column of city and state.

Read range the input excel and the split the row values using.


Use add datatrow activity.


write range the Dt.


@nirmalya.sarkar you can try this

Splitvariable = Split(row(“Location").ToString,”/")

City = Splitvariable(0).replace(":","").Trim
State = Split(Splitvatiable(1),"-")(0).Trim

if want pincode

pincode = Split(Splitvatiable(1),"-")(1).Trim

Happy automation :slightly_smiling_face:

Thank You @pravin_calvin. It’s done!!

But can you explain me this line “{Splitvariable(0).replace(”:",""),Splitvatiable(1)}"?? Specifically I want to know significance of (0) and (1).

Hi @nirmalya.sarkar

While splitting the string : Kolkata/West Bengal - 700001 based on slash

Its get separated as two values

Index(0) holding the value : Kolkata

Index(1) holding the value West Bengal - 700001


1 Like

@pravin_calvin , I have followed the steps which you said and it’s done. But, now I have to write these “city” and “State” column in a pre-existing excel file or write in a new page with whole data

What will be the steps??

Hi @nirmalya.sarkar

Please refer to the xaml below.

I have done with excel already having the column name city and state.

newSplitProcess.xaml (8.0 KB)


Hi @pravin_calvin , after messaging you, I am trying it for sometime. And it worked.
Btw, thank you for your constant support .

If I have any problem regarding automation in future, can I message you ??



Happy Automation!


1 Like

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