Sum of data containing dots and commas

[alper_ozyurt (Alper Özyurt)]

Hi all,

I need to sum the numbers in a column inside my data table. However, some numbers have dots and some numbers have commas.

Example;

Column1
234.56
845,44
585
645.56
845,55

Log Message : DT_Data.Columns(“Column1”).DataType.ToString = “Decimial”

Thank you

[Yoichi]

HI,

Can you share how you want to recognize comma and dot as? Are they always decimal separator? Or sometimes it may be thousand separator?

If former, the following sample will help you.

dt.AsEnumerable.Sum(Function(r) Decimal.Parse(r("Data").ToString.Replace(",",".")))

Sample
Sample20240909-2.zip (9.3 KB)

Regards,

[alper_ozyurt (Alper Özyurt)]

Thanks for the reply Yoichi.
Yes always decimal separator. Not used for thousands separation. (Exm:It can be 88054,45 or 88054.45)

But, When I run it as dt.AsEnumerable.Sum(Function(r) Decimal.Parse(r(“Data”).ToString.Replace(“,”,“.”))) I get error “Log Message: Input string was not in a correct format.”

I do not read data from an excel. Cata comes from database with Execute Query

[Yoichi]

Hi,

Is there any non-numeric data including empty in the column? If so, the following expression will work.

 dt.AsEnumerable.Select(Function(r) r("Data").ToString.Replace(",",".")).Where(Function(s) Decimal.TryParse(s,New Decimal)).Sum(Function(s) Decimal.Parse(s))

Regards,

[alper_ozyurt (Alper Özyurt)]

This worked Yoichi thank you very much.

Regards,