Split string to move number at end to new column

[padmashree.x.sandeep]

I have text something like this
Cash 2345612
Accounts receivable net 4567891
Contract asset - unbilled revenue 34563217

I want to copy the number to next column and write to excel like below

Please help me achieve this

[Yoichi]

Hi,

Can you try the following sample?

dt = System.Text.RegularExpressions.Regex.Matches(yourString,"(?m)(?<ITEM>^.*)\s(?<AMOUNT>\d+$)").Cast(Of System.Text.RegularExpressions.Match).Select(Function(m) dt.LoadDataRow({m.Groups("ITEM").Value,m.Groups("AMOUNT").Value},False)).CopyToDataTable

Sample20230503-4aL.zip (2.9 KB)

Regards,

[arjunshenoy]

Hi @padmashree.x.sandeep, welcome to the Community.

You can use the ‘Generate Datatable From Text’ activity to get the above-mentioned text data into a data table, let’s say 'rawDt'. You can do it like this:

→ Build a data table with 2 columns namely Cash & Num, let’s call it 'resultDt'.

→ Now, iterate through the rawDt & start fetching the cash description data & numeric data for each currentRow using the following RegEx:

cashData = System.Text.RegularExpressions.Regex.Match(currentRow(0).ToString,"^.*?(?=\s\d+$)").ToString

amountData = System.Text.RegularExpressions.Regex.Match(currentRow(0).ToString,"\d+$").ToString

Note: cashData & amountData are both of type string.

→ Once you have both data in variables, you can use Add Datarow activity to insert these data into the resultDt.

Hope this helps,
Best Regards.

[padmashree.x.sandeep]

To create the 2 variables with Regex, Should I use assign activity within for each row in data table activity

[padmashree.x.sandeep]

I am getting error “The source contains no datarows error” I assume this is because, a few row doesnt have number at the end (Like headers, sub headers etc). Not sure, if I will be able to eliminate them

[Yoichi]

Hi,

Can you share specific example which doesn’t have number at the end of string and expected result?

Regards,

[arjunshenoy]

@padmashree.x.sandeep

Yes, when you assign the regex extracted values to the variable inside the loop, you will have the extracted data from that specific datarow in the iteration. You can add the same to the datatable & the same process continues.

Best Regards.

[padmashree.x.sandeep]

Assets Current Assets Cash 2345612 Accounts receivable net 4567891 Contract asset - unbilled revenue 34563217 Liabilities Current Liabilities

something like this. Is there a way to apply the second logic if number is there, And if there is no number, default it to 0 may be

[padmashree.x.sandeep]

@arjunshenoy

System.Text.RegularExpressions.Regex.Match(currentRow(0).ToString,“\d+$”).ToString

This is working perfectly. But omiting few numbers that are in brackets. basically they are negative numbers. Is there a way I can copy negative numbers as well ?

Example data row:
Deferred Rent (123456)

[arjunshenoy]

@padmashree.x.sandeep

Well, in that case, you can use -?\d+(?=\D*$) instead of \d+$ from the previous expression. The updated one would be:

amountData = System.Text.RegularExpressions.Regex.Match(currentRow(0).ToString,"-?\d+(?=\D*$)").ToString

Hope this helps,
Best Regards.

[padmashree.x.sandeep]

@arjunshenoy

System.Text.RegularExpressions.Regex.Match(currentRow(0).ToString,“-?\d+(?=\D*$)”).ToString

The result now is exactly matching. Thank you so much Arun

[Gnanapriya_G]

Hi @padmashree.x.sandeep

You can use this pattern to extract the words before the numeric values: System.text.RegularExpressions.Regex.Matches(str,"[\D]+(?=\d+)")

To extract only numeric values use this pattern:
System.text.RegularExpressions.Regex.Matches(str,"\d+")

thanks
Priya