Seprate number from a string

Help Studio
, , ,
1

I have one balance column in datatable eg.
277.09CR.
123.45DR.
2,789.78DR.
I want to first seperate DR entries and then seperate number from DR Store in another datatable.

2

@Anil_Potekar

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

Cheers

1 Like
3

Hi,

Hope the following sample helps you.

image
image1106Ă—1608 53 KB 

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

Sample20230104-3L.zip (9.4 KB)

Regards,

1 Like
4

I’m using 2020.10.8 version

5

How about the following?

image
image710Ă—1418 34.9 KB

Sample20230104-1aL.zip (3.3 KB)

1 Like
6

hI @Anil_Potekar ,

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

or

System.Text.RegularExpressions.Regex.Match(YourString,“[1-9]+$”).Tostring.Trim

it will extract number from the string

Thanks

1 Like
7

Screenshot (39)
Screenshot (39)1920Ă—1080 264 KB

Trans.xlsx (9.9 KB)

8

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.

9

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

Regards

1 Like
10

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

11

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
12

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