Convert negative string to positive in Excel without using Loops

Help
, , ,
1

Hi,

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?

image

Amount.xlsx (10.1 KB)

2

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

3

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

4

@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

3 Likes
5

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

1 Like
6

The Linq can be used to update the column .

1 Like
7

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

8

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
9

Hello @indrajit.shah

You can easily do this using linq

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

4 Likes
10

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

2 Likes
11

@vickydas
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.

12

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
13

Thankyou.

How stupid of me :sweat_smile:

14

@vickydas
My Date column has been messed up

image
image737×127 8.94 KB

15

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
16

Perfect

17

IMG_20211220_173327
IMG_20211220_1733271920×2560 607 KB

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

@supermanPunch

18

Hi,

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