Separate one column into two value

[mashy2]

Hi,
I have a column name amount, with positive and negatives value.

How to separate the column into those with bracket and vise versa?

Thanks

[Lahiru.Fernando]

Hi @mashy2

You can follow the below steps.

• read range to get the data onto a datatable
• use add new data table column to add a new column to hold the split values
• use a for each row to loop through the datatable
• within the loop, you can use a if condition to check whether the value is a negative or not

Int32.Parse(Row(“ColumnName”).ToString) < 0

If so, use assign activity to add the value to new column and clear the value in the current column

Note: in the if condition why I gave it as <0 is because it looks like the brackets are there because of the column formatting of the excel. But in the formula bar, you might be having a negative value. So use the condition according to the value you have in the formula bar because excel formatting is not considered once the data is in a data table

Hope it helps…

[mashy2]

@Lahiru.Fernando

assign activity to add the value to new column and clear the value in the current column

Hi,

How to do this part?

assign activity to add the value to new column and clear the value in the current column

Thanks!

[Lahiru.Fernando]

in the For each row, and inside the IF condition

Assign the value to new column
row(“NewColumnName”) = row(“OldColumnName”)

Clear the value in old column
row(“OldColumnName”) = “”

[mashy2]

thanks so much