How to split text and number in the same cell to another cell by row

Hi guys,

Could you please tell me How I split text with number (no space) in “Time” to another row

I want to separate the “12 leCount” and “3”
Any If you can send the workflow to tell me, Thanks!! very kind of you.

Thank you in advance.
Screenshot 2023-10-10 153812

Hi @BeattbeatKittiya ,

Could you confirm whether you would require the Number value after Count in another Column or another row ?

image

we can use regex
(?<=count).*[0-9]

read excel
loop through data
in assigna activity

take a varaible of string data type in left side
and take the below in right value to save

system.text.regularexpression.regex.match(currentrow,“(?<=count).*[0-9]”).value.trim

Hope this helps

Thank for you reply,
I want the count value in another row that next to “Time” column

@BeattbeatKittiya

can you provide your expected output

cheers

If you want seperate column for count values ,
Here is the xaml file,
SeperateRowsColumns.xaml (12.1 KB)
image->output

Regards,

1 Like

Hi @BeattbeatKittiya ,

Input
image

Output
image

Workflow
image

Code

(From rows In dt.AsEnumerable
Let a = rows("Col1").ToString.Substring(rows("Col1").ToString.IndexOf("Count")+"Count".Length)
Let b = Split( rows("Col1").ToString,"Count")(0)+"Count"
Select dt.Clone.Rows.Add({ rows("Col1").ToString,a,b})).CopyToDataTable

Thanks,

1 Like

@BeattbeatKittiya ,

You could maybe check with the below Steps :

  1. Assuming you are reading the Excel sheet as Datatable, let say DT.

  2. We can add a new Column to the Datatable using Add Data Column Activity like below.

  3. Next, we can use the below Linq Expression to Perform the Separation of the values based on regex and adding it to another column.

DT = (From r In DT
Let m = Regex.Match(r("Time").ToString,"(.*?)(\d+$)")
Select DT.Clone.Rows.Add(m.Groups(1),m.Groups(2))).CopyToDatatable

Implementation :

Debug Panel :
image

@BeattbeatKittiya

your data was in BuildDt.
Build another Dt with 2 column as dt_filter

dt_filter=BuildDt.AsEnumerable.Select(function(x) dt_filter.Clone.LoadDataRow({System.Text.RegularExpressions.Regex.Split(x(0).ToString,“(?<=Count).“)(0),System.Text.RegularExpressions.Regex.Match(x(0).ToString,”(?<=Count).”).Value},False)).CopyToDataTable

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