Need help with Format Excel Activity

Help Studio
, ,
1

Hello community,

I need some help. I am working on a project where I want to analyze bank statements. I read the data from csv files and want to output it in an Excel file.

The numbers that are read from the csv can be formatted via Format Excel activity. This works for positive numbers as I have experienced. In case I have a negative number or with decimal sign (“,”) the formatting seems not to work.

Example:

“500” can be formatted to a number 500. That is what I want.
“-12,99” cannot be formatted and remains as unformatted data.

Can you please help me to find a custom format which solves that problem?
If possible I would take the format as currency format in “€” as well.

So with the example above I would like to have “-12,99 €” and “500,00 €” if possible. I am not familiar with Excel format builder and the builders I have found work with the american writing style “12.99” instead of “12,99”.

Thanks in advance.

2

Hi @SenorChang

Can you share sample input and expected output.

Regards

3

Hi @SenorChang

I had solved a similar query on converting a DE format decimal to Global. Could you check it:

Do customize it to your needs.

1 Like
4

Use this is format cell activity
#,##0.00\ €;[Red]-#,##0.00\ €

if they are read as text from the CSV, you may need to convert them
Try this
Convert.ToDouble(“your string”, New CultureInfo(“de-DE”))

or via assign activity
paarsedNumber = Double.Parse(row(“Amount”).ToString, New CultureInfo(“de-DE”))

output:-

500 500,00 €
-12,99 -12,99 € (in red)

And mark as solved if you found this solution is right.

1 Like
5

Hi @SenorChang

To format numbers like “-12,99” and “500” as Euro currency in Excel using UiPath, use the Format Cells activity with this custom format:

#,##0.00\ €;[Red]-#,##0.00\ €

This format supports European number style with commas as decimal separators and shows negative numbers in red with a minus sign. Make sure the cell values are first converted to numbers (e.g., using CDbl() or Convert.ToDouble() ) before formatting, as formatting won’t apply correctly to text. Also, ensure your system’s regional settings support the comma decimal format.

If you found helpful, feel free to tick as a solution.
Happy Automation

1 Like
6

Hey @SenorChang can you try with the following method

  1. Read the CSV
    Use Read CSV activity
    Output: dt_Statements (DataTable)
  2. Convert String Values to Decimal
    Use a For Each Row in DataTable activity:
    For Each row in dt_Statements
    Inside the loop, use an Assign activity:
    row(“Amount”) = Decimal.Parse(row(“Amount”).ToString.Trim(), New CultureInfo(“de-DE”))
  3. Write to Excel
    Use Write Range activity.
    Input DataTable: dt_Statements
  4. Format the Amount Column
    Use Format Cells activity.
    Sheet Name: your Excel sheet
    Range: e.g. “B2:B100”
    Format:
    #.##0,00 €;[Red]-#.##0,00 €
    Method to get the row count -“AmountColumnLetter” + (dt_Statements.Rows.Count + 1).ToString

cheers

1 Like
7

Thank you. I tried your approach and now it works. Very handy gimmick to color the text. Interestingly I had to modify the custom format due to my language settings.

The resulting format is: #.##0,00\ €;[Rot]-#.##0,00\ €

I even had to change the word “red” to “Rot” (german translation).

8

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.