Manipulate Data Table Column With Formula

[Wil_Lee]

I need to apply a formula to an entire column and copy into a new datatable. I don’t want to use a For Each loop since sometimes these tables have a LOT of lines.

I have a column where negative values have the “-” at the end, so I need an if statement to parse and convert to double. The if is pretty straight forward, but I’m not sure how to phase the Enumerable expression
Before:

Col1	Col2
Text1	123
Text2	123-
Text3	1234
After:
Col1	Col2
—	—
Text1	123
Text2	-123
Text3	1234

[Sagar_Gupta1 (sagar Gupta)]

Before:

Logic:

First use read Range activity for getting all value from excel and stored in datatable variable like(Ex: dt_test)

then use For Each Row Activity with indexnum integer type and put If Condition in for each row

then Use If Condition and condition is (row(1).toString.EndsWith(“-”))

Here I m using Row(1) for col 2.

Then use Write cell Activity for to get output like:

Thanks
Let me know still if you have any doubt

[Wil_Lee]

Hi, I said I don’t want to use a For Each activity. These tables can get really big and I’m looking for a more efficient way to do it.

[prasath17 (Guru)]

@Wil_Lee - Please check the below

string.Format(“{0}{1}”, Str.Substring(Str.Length - Length),Str.Substring(0, Str.Length - Length))

Output:

[prasath17 (Guru)]

@Wil_Lee - Any update on this?

[Wil_Lee]

No this only transforms the string which is not the issue here. The original ask was how to apply an efficient transformation on an entire datatable column. Efficient being, not using a for each loop as sometimes these tables have 50,000+ rows.