Convert excel column to 2 decimal places

Help Activities
, , , , , ,
1

Hi,

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

BEFORE:
image

AFTER:
image

Is that possible?
Can anyone help me please?
Thanks!
UIPath_Excel_Scratch.xlsx (129.8 KB)

2

@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…

image

Hope this helps…

3

Like that?
What should be my entries here?
image

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?

4

@Yudhisteer_Chintaram1 - Ok, please try like this…

image
image500×640 14.8 KB

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

image

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
5

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

6

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
7

Unfortunately, @prasath17 ,
I noticed an anomaly. I converted it to 2 DP first then 1 DP as shown below:
image

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:
image

What is the issue here>?

In your above screenshot, I found one anomaly too:
It should be 0.45
image

8

@Yudhisteer_Chintaram1 - After using “MidpointRounding.AwayFromZero” issue has fixed…

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

Please see the ouput below:

image
image467×530 7.28 KB

1 Like
9

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