Text to column activity issue in Uipath

[monisharpa2020]

Hi,

In an excel , I have a column value like given below,
8355-
19,722-
15,056-
524.87
292.33-
3.10

usually when we do text to columns in excel these negative sign in the suffix will go to the front, but the same way when I do the Text to columns in UiPath these are not formatted, please advise
also not all value are negative in the column.

[pikorpa (Piotr Kołakowski)]

Hey @monisharpa2020
Excel automatically moves the negative sign to the front when using Text to Columns, but UiPath doesn’t.
You can try use a For Each Row loop to check if a value ends with “-”, then move the “-” to the front:

If 
CurrentRow("ColumnName").ToString.Trim.EndsWith("-") 
Then  
CurrentRow("ColumnName") = "-" & CurrentRow("ColumnName").ToString.Trim.Substring(0, CurrentRow("ColumnName").ToString.Trim.Length - 1)

[lrtetala (Lakshman Reddy)]

Hi @monisharpa2020

Can you try the below

Code:

For Each row As DataRow In dt.Rows
    Dim value As String = row("Amount").ToString.Trim()
    Dim isNegative As Boolean = value.EndsWith("-")
    value = value.Replace(",", "").Replace("-", "")
    row("Amount") = CDbl(value) * If(isNegative, -1, 1)
Next

Regards,