Hi, how to separate text in Excel? I have several rows in the column like:
1. Name and surname - I would like to separate the “1.”, so that the number is in one column and the name with the surname in the other. And ideally, the number should be without a dot.
That is:
the first column: a digit, e.g. 1
the second column: name and surname
Thank You! 
Hi @sullivanne
Could you please share sample input and output so that we can give a solution for you.
Regards,
Now is:
Should be:
Separate the digit from the name and remove the dot.
Hi @sullivanne
=> Use Read Range Workbook to read the excel and store it in DataTable say dt_data
=> Use For Each Row in Data Table to iterate through data table and give the below syntaxes in assign activity:
Name= CurrentRow("Name and surname").ToString
CurrentRow("Number")= System.Text.RegularExpressions.Regex.Match(Name,"^\d\.+").Value.Replace(".","")
CurrentRow("Name and surname")= System.Text.RegularExpressions.Regex.Match(Name,"(?<=^\d\.+\s).*").Value
=> Use Write Range Workbook to write it back to excel.
Check the below zip file for the workflow for better understanding.
Sheet1 is the input and Seet2 is the output which is in your required format.
Forum Question.zip (67.6 KB)
Hope it helps!!
@sullivanne
1.build dt_filter with the desired feilds.
assign to dt_filter=DT.AsEnumerable.Select(Function(r) dt_Filter.LoadDataRow({r(“ColumnName”).ToString.Split(".“c).First,r(“ColumnName”).ToString.Split(”."c).Last},False)).CopyToDataTable