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)
System.Text.RegularExpressions.Regex.Match(item.ToString,“[B][a][r][o][d][a][A-Z][a-z].+”).ToString

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

Please help.

Hi,

Hope the following sample helps you.

img20220425-1

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)

Regards,

Sir

Kindly explain your code at Regex Format

Hi,

"(?<=\))\s*?\n"

(?<=)) 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.

Regards,

Regards,

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