Convert negative string to positive in Excel without using Loops


In my working sheet, the column name is “Amount” and it holds number in positive as well as negative.

-I am trying to convert all the -ve numbers in that column to positive as I have to compare those data with another data.
-I am reading the full excel sheet and storing it in a DT, so how can I convert the value?


Amount.xlsx (10.1 KB)

@indrajit.shah For Converting negative numbers to Positive, you can use Math.Abs(yourNumber)

Ya but how can i utilize it in DT and write the new data in a sheet .

@indrajit.shah Can you follow these Steps :

  1. Use Read Range Actvity read your Excel File, Get Output Datatable, say DT
  2. Use For Each Row Activity For that DT
    Inside For Each use An Assign Activity and Assign row(“Amount”) = Math.Abs(Convert.ToDouble(row(“Amount”).ToString))
  3. Use Write Range Activity and Write the DT back to the Same Excel File

For Clarification Purpose, I recommend to use a Different Excel File as Output


I am looking for suggestion other than using loop.
but its good solution.

1 Like

The Linq can be used to update the column .

1 Like

Do you mind sharing?
as once the data is updated i have take the data to work further.

How to drag a formula in the entire column in excel - #2 by ClaytonM
Can you try this. Even in Linq the For loop has to be used .

1 Like

Hello @indrajit.shah

You can easily do this using linq

Check this workflow for better understanding
-ve to ve.xaml (6.8 KB)


I think if you only want this to be applied to your excel file, just use the ABS function in that column.


cool, I didn’t tried linq in UiPath.
i have a question too.

-The output of the “Output Data Table” Activity is string and so i can’t able to write the output in new excel.

hello @indrajit.shah

Output Datatable activity is used to convert the datatable to String
Why are you using Output Datatable activity if you don’t want it in string

Delete the Output Datatable activity and use write range activity with the Datatable type variable as Input

1 Like


How stupid of me :sweat_smile:

My Date column has been messed up

Hello @indrajit.shah
this is happening because of your excel sheets as the cells are formatted in Date so you get the date with time when you write it using Write Range Activity

Replace the String.Join(Query) in the code with this

String.Join("|",DT.Columns.Cast(Of DataColumn).Select(Function(col) If(CStr(col.columnname)="Amount",x,If(CStr(col.columnname)="Date",CDate(roww.Item("Date")).ToShortDateString,roww.Item(col.columnname))))).Split("|"c)

and run your code let me know about it

1 Like


See this excel sheet I need to change - to +
And + to - how can we acheive this
For writing query
I try your above query however - value is changed to + but + value is not at all changed



The =SUBSTITUTE(SUBSTITUTE(A1,“-”,“”),“+”,“”) can be used to substitute the + and - ve value and use the Autofill range to fix the rest of the values.

1 Like