Convert number text to number in excel and how to sum up the column?

The numbers in the column are in text format in excel. I would like to calculate the amount for each individual column. How do I convert the text to number format and then calculate the sum of the column?
(the column length is always different for different spreadsheets, hence I would not be able to use “Type Into” and excel formula)
Thanks in advance!

@kieranwong

  1. First use Read Range activity to read the data from Excel and will give you output as Datatable and say DT.

  2. And then try below expression.

             String sumOfColumn = DT.AsEnumerable.Sum(Function(x) Convert.ToDouble(x(“ColumnName”).ToString.Trim) ).ToString
    
  3. And then write into excel file.

5 Likes

what activity in Uipath do i use for step 2?

it’s Assign

1 Like

Can you explain the code to me?

@kieranwong

We have to use Assign activity.

It will calculate sum of column values mentioned in that from the given input datatable. And then it will convert that value into string. so that you can write into excel file using Write Cell activity.

Sorry for the late reply, but I have an error in my Assign activity. I think it is because the column name cannot be read as it is not really a table but a pasted table. Is there a way where i can use for each activity? @samir @lakshman

1 Like

If I only had to convert the text to integer then how should I proceed?

Thanks!

@Niharika_Sharma

Use CInt(“Text”). It will help us to convert text to Integer.

code is not working …throw exception… could you help me?