Breakdown string in one cell into different cells in excel

Help Activities
,
1

breakdown string in one cell(first screenshot) into different cells(second screenshot) in excel

image
image1447×205 7.8 KB

image
image1671×415 27.2 KB

May I know the best and most reliable way of doing this?

2

@Krithi1

Please follow the below steps…this can be used even if rows increase

  1. Read the data into datatable dt and creeate one more datatable newdt and use newdt = dt.Clone() in assign
  2. Use for each row in datatable(currentrow) on dt
  3. Inside that use a for each loop(item1) for looping theough values in Days column. Currentrow("Days").Tostring.Split(";",Stringsplitoptions.RemoveEmptyEntries).ToArray… make sure to change type argument to string
  4. Inside that use another for each loop(item2) for looping through values in times column. Currentrow("Times").Tostring.Split(";",Stringsplitoptions.RemoveEmptyEntries).ToArray… make sure to change type argument to string
  5. Inside that use a add datarow and give newdt and row values would be
    {currentrow("Name").Tostring,currentrow("Email").Tostring,item1,item2}

Hope this helps

Cheers

3

Hi,

Hope the following sample helps you.

image
image748×491 21.9 KB 

dt = dt.AsEnumerable.SelectMany(Function(r) r("Days").ToString.Split({";"c},StringSplitOptions.RemoveEmptyEntries).SelectMany(Function(d) r("Times").ToString.Split({";"c},StringSplitOptions.RemoveEmptyEntries).Select(Function(t) dt.Clone.LoadDataRow({r(0),r(1),d,t},False)))).CopyToDataTable

Sample20230114-2aL.zip (2.9 KB)

Regards,

4

@Anil_G - this is how I did it currently, but I wasted to check if there are any better options

1 Like
5

I will try this, thank you @Yoichi