Forum Discussion

Copper Contributor

Mar 09, 2023

Cell has a list wrapped in html. Split list into 2 separated by special character

I have an excel file that has a list of items that are wrapped in html code. I would like to split it into 2 groups and move them into 2 different cells and separate the list with a special character...

PlanDetailExport.xls27 KB

excel

peiyezhu

Bronze Contributor

Mar 10, 2023

parse like this?

http://e.anyoupin.cn/bsbm/pdo/eh2/a2502/mybookmark/sql/sql/display_table_t.php?find=+create+temp+table+aa+as+%0D%0Aselect+*%2Cudf_strip_tags%28%60Long+Description%60%29+plan++from+Split_list_into_two_separated_by_special_character%3B%0D%0Aselect+%60Long+Description%60%2Cregexp2%28%27Includes%3A%28%5B%5Cs%5CS%5D*%29Optional%27%2Cplan%2C1%29+Plan_inc%2Cregexp2%28%27Optional%5B%5Cs%5CS%5D*Include%3A%28%5B%5Cs%5CS%5D%2B%29%24%27%2Cplan%2C1%29+Option_inc+from+aa%3B%0D%0A+%0D%0A+Long+Description

peiyezhu

Bronze Contributor

Mar 10, 2023

create temp table aa as
select *,udf_strip_tags(`Long Description`) plan from Split_list_into_two_separated_by_special_character;
select `Long Description`,regexp2('Includes:([\s\S]*)Optional',plan,1) Plan_inc,regexp2('Optional[\s\S]*Include:([\s\S]+)$',plan,1) Option_inc from aa;