SUM OF VALUES IN A COLUMN IF CONTAINS A CHAR

Help Studio
, ,
1

Hello how can i get the sum of items in a column with parenthesis/ or negative values.

image
image122Ă—531 2.03 KB

2

in general we can extract Numbers, Group /Decimal Seperator, minus with a Regex

grafik

[CheatSheet] - System.Text.RegularExpressions | RegEx - News / Tutorials - UiPath Community Forum

3

Try this-

-?\d+(?:.\d+)?|(\d+(?:.\d+)?)

4

Hello @jhenesie.t.tapalla
Try Regex method

System.Text.RegularExpressions.Regex.Match(YourString,"[\d.-]+").Tostring.Trim

To replace the parenthesis with a negative sign, follow the steps

  1. Read Excel and store it in the data table.
    2 Use For each loop data table
    2.1 In which, use the IF condition as Currentrow(“Net of VAT”).ToString.contains(“(”)
    then—> use assign
    Currentrow("Net of VAT")=Cdbl("-"+ System.Text.RegularExpressions.Regex.Match(Currentrow("Net of VAT").ToString,"[\d.-]+").Tostring.Trim)

image

1 Like
5

@jhenesie.t.tapalla

Ideally if you try to read the excel using workbook ramge woth preserve formatting unchecked or use excel file read rangw using rawvalues …then the items would be read as number only and without brackets…

Then you can use the following to get the sum

Dt.AsEnumerable.Where(function(x) cdbl(x("ColumnName"))<0).Sum(function(x) Cdbl(x("ColumnName")))

Cheers