正则表达式提取内容的疑问

arnosue

新人又来麻烦各位大佬了。

最近在学习正则表达式，正好公司有整理系统SKU的需求，自己刚学点皮毛，只会去除中文和空格，实在应付不来。还望坛内大佬百忙之中，不吝赐教。

SKU整理标准已在表中注明，如下图。学习中的疑问如果能提供解答或者相关帖子就更好了，感激不尽！

本人使用的是WPS最新版。

wp231957

我觉得需求有些多，用公式有些繁琐，不如写脚本 来的方便

hxj119

1. =REGEXP(REGEXP(CONCAT(REGEXP(SUBSTITUTES(A2,{"*","(",")","|","°","_","=",".","&","+","。",",","/"},{"x","","","","","-","-","-","-","-","-","-","-"}),"[A-Za-z\d-]+")),"-{1,}",2,"-"),"-$",2,"")

复制代码

arnosue

hxj119 发表于 2024-8-14 12:40

完美，膜拜大佬。原来标点符号需要这样穷举替换再提取，受教了

hxj119

更新一下，优化公式：

1. =REGEXP(REGEXP(SUBSTITUTES(A2,{"*","(",")","|","°"},{"x","","","",""}),"[^A-Za-z\d\-]|\-{2,}",2,"-"),"\-{2,}",2,"")

复制代码

arnosue

hxj119 发表于 2024-8-14 13:37
更新一下，优化公式：

多谢，大概看懂了，优化主要是针对标点符号部分对吧

jerrike

1. =TEXTJOIN("-",1,REGEXP(SUBSTITUTES(A2,{"*","(",")","|","°"," ","_"},{"x","","","","","","-"}),"[\w]+"))

复制代码

arnosue

hxj119 发表于 2024-8-14 13:37
更新一下，优化公式：

请教一下，为何优化后的公式会对空格处理的不好。例如截图中，标黄的第一列为原公式结果，第二列公式为优化公式结果，优化后好像是把空格替换为单个"-"。

hxj119

1. =REGEXP(REGEXP(REGEXP(SUBSTITUTES(TRIM(A2),{"*","(",")","|","°"," "," "},{"x","","","","","",""}),"[^A-Za-z\d\-]|\-{2,}",2,"-"),"\-+",2,"-"),"-$",2,"")

复制代码