Seprate number from a string

I have one balance column in datatable eg.
I want to first seperate DR entries and then seperate number from DR Store in another datatable.


To get DR only use filter datatable woth contains filter

To get only number…use loop and ypu can use a str.Replace("DR","") here str is input string in loop it will be currentrow(column name)

Or use linq as below in assign

Dt = Dt.AsEnumerable.Where(function(x) x("ColName").ToString.Contains("DR")).Select(function(x) x("ColName").ToString.Replace("DR","")).CopyToDatatable


1 Like


Hope the following sample helps you.

arrDR = dt.AsEnumerable.Where(Function(r) r("balance").ToString.Contains("DR")).Select(Function(r) dtResult.LoadDataRow({System.Text.RegularExpressions.Regex.Match(r("balance").ToString,"[.,\d]+").Value},False)).ToArray (9.4 KB)


1 Like

I’m using 2020.10.8 version

How about the following? (3.3 KB)

1 Like

hI @Anil_Potekar ,

if it is variable has CR or DR then you can use Right(Variable,2)



it will extract number from the string


1 Like

Trans.xlsx (9.9 KB)

Hi @Anil_Potekar ,

Could you also provide us with the Expected Output that you need from the Provided input data.

In this way, We could get a clear idea on the logic that would need to be applied to the data.

Hi @AnilPotekar

You can try this solution-

  1. Use assign activity and create a variable OutputDt of datatype datatable and initialize this variable as follows:

OutputDt = InputDt.Clone

  1. In the next Assign Activity Write the expression as mentioned below-
OutputDt = (From r In InputDt.AsEnumerable Let br =r.ItemArray.Take(1).ToList().Cast(OfObject).Concat(r.ItemArray.Skip(1).Take(1).ToList().ConvertAll(Function(x) x.ToString.Replace("DR",""))).Toarray Select OutputDt.Rows.Add(br)).CopytoDatatable


1 Like

Output from table max value of DR entry is 2,789.78CR.

Hi Anil_Potekar

To Find the Max value from the “balance” Column of datatable, use the expression in assign Activity-

MaxValue = OutputDt.AsEnumerable.Max(Function(x) Cdbl(x(“balance”)))

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.