Breakdown string in one cell into different cells in excel

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

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


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

Hope this helps



Hope the following sample helps you.

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 (2.9 KB)


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

1 Like

I will try this, thank you @Yoichi