How to split 1 excel cell into 2 other cells

Hi there,

If i have an Excel file as in the attachtment, how can I split the first column (for each row, besides the header) so that the cells ‘firstname’ and ‘lastname’ will be filled with the correct data?

Thanks in advance!!



use for each row in datatable activity

inside use

2 assign activity

currentrow(“firstname”)=currentrow(“full name”).tostring.split(" "c).first

currentrow(“lastname”)=currentrow(“full name”).tostring.split(" "c).last

outside of for each row in datatable activity use

write range activity to write back to the excel


one of many options

  • read range Activity - dtData
  • For each row in DataTable activity | row in dtData
    • Assign Activity: arrSplits = row(“full name”).toString.Trim.Split(" "c)
    • Assign Activity: row(“firstname”) = arrSplits.First()
    • Assign Activity: row(“lasttname”) = arrSplits.Last()

arrSplits is a string Array

So i created 2 variables. ‘firstname’ and ‘lastname’. Is that correct? No errors showing, until I added the ‘write range’ activity. It says ‘string cannot be converted into datatable’. What am i doing wrong?

as you are using read range for reading the excel file

write range activity should be used outside of for each activity

and for write range you need to pass the output of read range datatable variable

you can try this way also

use read range workbook activity to read the excel file create output as dt

take assign activity

dt=dt.AsEnumerable.Select(Function(r) dt.Clone.LoadDataRow({r(0).ToString,r(0).ToString.Split(" “c).First,r(0).ToString.Split(” "c).Last},False)).CopyToDataTable

use write range workbook activity and pass the dt as showing in the below image


i understand, but the variables created in the for loop aren’t approachable outside the for each activity. Do I need to create 2 datatables in stead of 2 variables?

do I need to create 2 write range workbook activities? 1 for the firstname and 1 for the lastname? Because if i write ‘dt’ to excel how does he knows wich part of the splitted string to paste in there?


that assign activity variables are no need to used outside of for each

can you share a sample input excel i will send the code