How to split 1 excel cell into 2 other cells

Help Studio
, ,
1

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!!

afbeelding

2

@tech2

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

cheers

3

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

4

afbeelding
afbeelding1609×792 55.5 KB

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?

5

@tech2
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
cheers

6

@tech2
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

image
image1065×593 25.2 KB

cheers

7

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?

8

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?

9

@tech2

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

cheers