How to spit a line in one excel column and put part of it another column

I have a data scrap that create columns in an excel file. I have added the column name but need to take this line “/title/tt0111161/?ref_=adv_li_tt” and have the tt011161 moved to a new column. This is a different value in each row. For the life of me I can not figure this out.

can you provide 2-3 examples of your rows? if they have the same structure, than you can try to use split or RegExp. For example the following will return tt0111161

split("/title/tt0111161/?ref_=adv_li_tt","/")(2)

Here are the top 20 lines of the spreadsheet
/title/tt0111161/?ref_=adv_li_tt
/title/tt0068646/?ref_=adv_li_tt
/title/tt0468569/?ref_=adv_li_tt
/title/tt0071562/?ref_=adv_li_tt
/title/tt0167260/?ref_=adv_li_tt
/title/tt0110912/?ref_=adv_li_tt
/title/tt0108052/?ref_=adv_li_tt
/title/tt0060196/?ref_=adv_li_tt
/title/tt0050083/?ref_=adv_li_tt
/title/tt1375666/?ref_=adv_li_tt
/title/tt0137523/?ref_=adv_li_tt
/title/tt0120737/?ref_=adv_li_tt
/title/tt0109830/?ref_=adv_li_tt
/title/tt0080684/?ref_=adv_li_tt
/title/tt8108198/?ref_=adv_li_tt
/title/tt4633694/?ref_=adv_li_tt
/title/tt0167261/?ref_=adv_li_tt
/title/tt0133093/?ref_=adv_li_tt
/title/tt0099685/?ref_=adv_li_tt

One of possible solutions: Split.zip (10.0 KB)

It simply reads XLSX file to the DataTable (DT) -> Adds New Column To DT -> iterates through DT and adds extracted value to the second column -> write DT to excel.

Of course it is possible to write directly to excel to the specific column. It depend on what exactly you are looking for.

1 Like

@Egleason

Let us take you are having a datatable dta
Let us take new DataTable dtb=dta.Clone
Add New Column Called Code to dtb

Now use Below Linq

dta=  (From p in dta.Select
      Let x= string.Join(",",p.ItemArray)+","+System.Text.RegularExpressions.Regex.Matches(p("ColumnName").ToString,"(?<=title\/).*?(?=\/)")(0).Value.ToString
       Select dtb.Rows.Add(x.Split({","},StringSplitOptions.None)).ToArray.CopyToDataTable

Regards,
Mahesh

All, Thank you for the response, @madsps thank you for your help.