Excel column Formatting issue - [simple but hard]

[hacky (NotHacker)]

Hello Team,

I am working on my project which demands the column “Acquisition” into the number but this is not working when I do the following:

1. Use Excel file activity
2. Macro VBA Script
3. UI Automation

Please help experts
@Yoichi , @Gokul001, @Anil_G

[Gokul001 (Gokul Balaji)]

Hi,

Have you tried with Format cell activity?

Regards
Gokul

[Yoichi]

HI,

Can you share the above sample as file?

Regards,

[hacky (NotHacker)]

Shared in DM. Please check out and let me know it is a simple requirement.

[hacky (NotHacker)]

Shared the file in DM. Please check out and let me know it is a simple requirement.

[Yoichi]

Hi,

How about the following?

Decimal.Parse(CurrentRow("     Acquisition").ToString.Trim,New System.Globalization.CultureInfo("es-ES"))

Note: DisplayValue is used in ReadRange activity

Regards,

[hacky (NotHacker)]

Can you please provide stronger solution?

Because I do not want to use For loop
Also, I do not want to use IO operations like Read, write to manipulate data.

I want solution with VBA script (Macro) or LINQ to optimize the code effectively.

In real time I shall be using 100s of files, so IO operations and Looping is not recommended.

[Yoichi]

The above ForEachRow is just for write value into output panel. If you need LINQ the following will work.

dt.AsEnumerable.ToList().ForEach(Sub(r)
    r("     Acquisition") = Decimal.Parse(r("     Acquisition").ToString.Trim,New System.Globalization.CultureInfo("es-ES"))
End Sub
)

Regards,