Splitting cell value into two row items when extracted as a single cell with new lines

Sample.xlsx (8.3 KB)
Hi All,
I have done data scraping from a PDF which is extracted as a single cell with new lines for only few data, while the rest is done correctly. I have attached the sample excel with expected output. using Regex and split function

Scheme_Name_Dup.Split(New String() {Environment.NewLine},StringSplitOptions.None)

but i am able to get only the first line before the paranthesis and the second line item is blank.

Please help.


Hope the following sample helps you.


dt = dt.AsEnumerable.SelectMany(Function(r) System.Text.RegularExpressions.Regex.Split(r("Scheme_Name").ToString,"(?<=\))\s*?\n").Select(Function(s) dt.Clone.LoadDataRow({s.Replace(vbCr,"").Replace(vbLF,"")},False))).CopyToDataTable()

Sample20220425-6.zip (8.4 KB)



Kindly explain your code at Regex Format



(?<=)) means positive lookbehind and it will match after ( character. \s*? matches 0 or more whitespace and \n means linebreak (LF).
So this expression will split by 0 or more whitespace and linebreak after ( character.

The following also helps you.



Thanks Sir

Once I implement my main code and if any queries I share you Sir.

Thanks a lot gave solution with pretty explanation
Have a Great Day

1 Like