How to split data in one column to another column

Hi Experts,

I generate data table from unstructed data (text files) and write data table into excel. Some of the data are sparated by tabs and some are separated by space. I used tab separated format and I got the following data into excel.

As you see, the first row is correct format and data is inserted into correct way.
For the second row, I want “260,000.00” to column G, “-1,444,370,00” to ColumnE and “-1,444,370.00” to column F.
The second row and third row are the same format so I want as the second row solution.
For the fourth row, I want “-210,200.00” to ColumnE, “-210,200.00” to ColumnF and “-21,000.00” to ColumnG.
I also upload the input format and the output format I want as attached.
Input_format.xlsx (9.2 KB)
output_format.xlsx (9.2 KB)

If thre is any solution or workaround, please let me know. Thanks.

Hey @Zaw_Win_Htun1,

If you are using modern activities, there is an activity called Text to Columns you can use that.


Hello @Zaw_Win_Htun1 ,

As input is a Unstructured string and as per given screenshot there’s no additional space in data, so you can perform the string operation of replace all the Tabs with spaces
then pass this string to generate data table activity and use space as a column separator. (as all the tabs replace by space now)

Hope it helps!