Leave only the integers in a column

[jntrk]

Hey everyone,

I want to remove every character in the cells of a column and leave only the integer values.

For example if the datatable is like this:
Number----Equals
45-----21%+
30-----15%+

I want to do this to the “Equals” Column
so output DT should be:

Number----Equals
45-----21
30-----15

And I dont want to use for each row because the excel I have has around 10k rows.

Any suggestions?

[NIVED_NAMBIAR (NIVED N)]

Hi @jntrk

Try this way

1. Use read range to read the datatable and store in dt1

2. use assign activity like this

dt2 = dt1.AsEnumerable().Where(Function(row) System.Text.RegularExpressions.Regex.Replace(row(“Equals”).ToString, “[^\w\d]+”, “”)).CopyToDataTable

Try this

[jntrk]

@NIVED_NAMBIAR

dt1.AsEnumerable().Where(Function(row) System.Text.RegularExpressions.Regex.Replace(row(“Equals”).ToString, “[^\w\d]+”, “”)).CopyToDataTable

It gives the error Option Strict on Disallows implicit Conversion from “String” to Boolean

[jntrk]

Why this error message is given? Do you have any idea?

[monsieurrahul (Rahul)]

Check this link:

Type Into activity implicit conversion on input parameter to string

Change Type Into activity so that it accepts any data type for the input parameter (System.Object instead of System.String) and implicitly convert to a string. Now we have to explicitly convert to a string via the “ToString” method whenever we want to type a number, DataTable Item, etc and I think this is redundant and could be avoided.

[jntrk]

I couldnt figure out how this will help the problem I face? I read it but I couldnt find a solution for this problem

[ppr (Peter Preuss)]

The conversion issue comes from the linq Statement. the Lambda Expression within the where Statement requires a Return of boolean which is Used for the Filter Evaluation.

The given predicate Returns a String and this is reason why IT comes to this validation Message.

Updating a column value with the Help of linq within an assign Activity requires some exploits. For Setting up such a Statement IT would be helpfully for ja that you will Share the complete datatable column structure with US.

Thanks

[jntrk]

Sure.

There are 8 columns in the datatable. the column I want to alter is named “Percent” it consists of values such between 0%-300% and the values come from a formula written inside the column cells but when I read range it takes the values not the formula, besides I dont need it to read the formula just the values inside the cells. Is there anyother info you need?

[jntrk]

Any news? I still couldnt make it work.

[jntrk]

I am attaching a sample excel.sample.xlsx (9.3 KB)

[NIVED_NAMBIAR (NIVED N)]

Hi @jntrk

Sorry for delayed response

Try this in invoke code

dt1.AsEnumerable().ToList().ForEach (Sub(row) row(“Equals”)=System.Text.RegularExpression.Regex.Replace(row (“Equals”).ToString,“[^0-9]+”).Value)

Hope it helps you

Regards

Nived N

Happy Automation