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


Here are the top 20 lines of the spreadsheet

One of possible solutions: (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


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


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