As per your input, This kind of output is not possible. Because length of the name will change every time. you specified name length 6 but if you see length of the second row name is 4. If is there any space between that text then its possible to do that.
Eg: Input: Johnny M 15 19880201
In this case, we can split text based on space as delimiter.
The character is fixed ( include space as 1 character)
Therefore for 2nd row, there are 2 space before Mary in my original datatable column, making it 6 character.
Same as the age column, there is a space before the age if age <10, making it to have fixed length of 2 character
If I do it by excel, I can just use text to column with fixed length delimiter to seperate the columns, but datatable I am not sure how to do
It is possible to get the output what you are expecting. You can’t split it from Left to Right but you can do it from Right to Left. like First extract Date and then Age and then Sex at last Name. In this way we no need to bother about Name length.
Attached workflow which will give you desired output and which will write into sheet2 of SampleInput file.