 # Convert excel column to 2 decimal places

Hi,

I have this excel column and I need to make all the numbers in the column “NW Elasthane” to 2 decimal places.

BEFORE: AFTER: Is that possible?
Thanks!
UIPath_Excel_Scratch.xlsx (129.8 KB)

@Yudhisteer_Chintaram1 - Create a new column Say X and using the write cell activity write the below formula…and then use Auto fill range to complete it to the subsequent cells… Hope this helps…

Like that?
What should be my entries here? The problem is I have a bigger excel with lot of columns. I dont know the index of the column “NW Elasthane”. What can I do in that situation?

@Yudhisteer_Chintaram1 - Ok, please try like this…

Invoke Code:

``````dt.AsEnumerable().ToList().ForEach(Sub(row) row("NW Elasthane 2DP")=Math.round(cdbl(row("NW Elasthane")),2).ToString("N2"))
``````

OR (if you want to write the output in Number format use the below…tested and working)

``````dt.AsEnumerable().ToList().ForEach(Sub(row) row("NW Elasthane 2DP")=Cdbl(Math.round(cdbl(row("NW Elasthane")),2).ToString("N2")))
``````

My Output you can use write range and right the output to a new sheet or the existing one…For example I have printed that here…

Hope this helps…

1 Like

Hi @prasath17 ,
What does this do: .ToString(“N2”))?

Hi @Yudhisteer_Chintaram1 - please find the reference below…

If the above solution solved your query…could you please mark my post as solution…thanks

1 Like

Unfortunately, @prasath17 ,
I noticed an anomaly. I converted it to 2 DP first then 1 DP as shown below: However, 0.145 should be 0.15 to 2dp then 0.2 to 1 dp.
But it is not the case.
However in another row below I had this value and it got converted well: What is the issue here>?

In your above screenshot, I found one anomaly too:
It should be 0.45 @Yudhisteer_Chintaram1 - After using “MidpointRounding.AwayFromZero” issue has fixed…

``````Math.Round(cdbl(row("NW Elasthane")),2,MidpointRounding.AwayFromZero).ToString("N2"))
``````