Create a new numerical column in excel with a format of 2 decimal places

[rk83]

I have a column in Excel with positive and negative values and I have to create a new column for the positives and another for the negatives. I am using one if and after the action "convert text to number" and after "convert number to text" and I choose the 2 decimal places that I need to present but if I select the thousand separator the PAD write to excel for example this result 10 000.11 but without the thousand separator I get this this wrong result 10000.1 with just 1 decimal place (the generated variable, FormattedNumber, have 2 decimal places in PAD).

Can anyone solve this problem because I need to present the value like this 10000.11?

And after I need to present the negative values in this format 0.00 but when I do the same transformations explained above the value that appears in the variable %FormattedNumber% is 0.00 however in Excel it appears just 0.

[Nived_Nambiar · Accepted solution]

Hi @rk83 

This would happening due to excel format issue, if you try manually in that column where your flow is entering the number, it would reduce the number of decimal places as you are seeing now (like 20.20 to 20.2). So to avoid that we have to make excel column format as 0.00 type . for that run a vbscript which set the column format as numbers with two decimal places. 

So add this sequence of code before your code which you are running 

First we are using two set variable option to set excel file path (first one) and other for setting which column we need to format, here we are specifying the excel column letter like A, B,C instead of column.

Now use run vbscript action to run below vbscript 

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True ' Set this to True if you want to see Excel working
Set objWorkbook = objExcel.Workbooks.Open("%ExcelFilePath%") ' Replace with the path to your Excel workbook
objExcel.DisplayAlerts = False
' Specify the column letter (e.g., "A") to format
columnToFormat = "%ExcelColLetter%"
decimalFormat = "0.00" ' Format to display two decimal places

' Get the worksheet you want to work with
Set objWorksheet = objWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with your sheet name

' Set the format for the entire column
objWorksheet.Columns(columnToFormat).NumberFormat = decimalFormat

' Now, you can enter data into the formatted column
'objWorksheet.Range(columnToFormat & "1").Value = 123.456 ' Example data

' Close the workbook and save changes
objWorkbook.Close True

' Quit Excel
objExcel.Quit

' Release object references
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing

The variables which we have specified earlier will be passing to this vbscript as u will see in script.

Once script completes execution, then place your current script below of this.

Then it would work, it would be considering the format as 0.00

Hope this helps !

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌