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.

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

Use add datatrow activity.

{Splitvariable(0).replace(":",""),Splitvatiable(1)}

write range the Dt.

Regards

@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

Regards

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)

Regards

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 ??

@nirmalya.sarkar

Yes!

Happy Automation!

Regards

1 Like

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