使用 Excel 的 IF、FIND 和 MID 函数将条形码转换为特定格式

Question

在 Excel 中，我有许多包含不同类型字符的列，例如：

WS-S5-S-L1-C31-F-U5-S9-P14 
WS-S5-S-L1-C31-F-U5-S8-P1 
WS-S5-N-L1-C29-V-U16-S6-P6 

我想使用以下规则将它们转换为 8 个字符：

• 仅保留最后三段
• 删除 U 并在适当的位置添加前缀 0
• 删除 S 并在适当的位置添加前缀 0
• 删除 P 并在适当的位置添加前缀 0

例如：

• WS-S5-S-L1-C31-F-U5-S9-P14转换成05-09-14
• WS-S5-S-L1-C31-F-U5-S8-P1转换成05-08-01
• WS-S5-N-L1-C29-V-U16-S6-P6转换成16-06-06

我相信有一种方法可以使用IF、FIND&MID函数在 Excel 中转换这些，但不知道如何开始。任何帮助都将不胜感激。

更新

最后，如果可能的话，我想将其转换为 13 个字符，例如：

• WS-S5-S-L1-C31-F-U5-S9-P14 转换为 S1-F-05-09-14
• WS-S5-N-L2-C31-D-U5-S8-P1 转换为 N2-D-05-08-01
• WS-S5-N-L1-C29-V-U16-S6-P6 转换为 N1-V-16-06-06

Answer 1

正如@ygaft 指出的那样，这是可能的，但是使用标准 Excel 函数会花费很长时间。

我使用免费RegEx 查找/替换在这种情况下添加插件，使用正则表达式可以更轻松地实现它。

公式：
=RegExReplace(RegExReplace(A1,".*U([0-9]+)-S([0-9]+)-P([0-9]+)","0$1-0$2-0$3"),"0([0-9]{2})","$1")

怎么运行的：

• 内部函数：
  • A1：来自 A1 单元格的内容
  • ".*U([0-9]+)-S([0-9]+)-P([0-9]+)"寻找模式“...U#-S#-P#”，其中“#”代表一个或多个数字并记住这些数字（括号创建参考组）
  • "0$1-0$2-0$3"合并上一步找到的数字，并为所有数字添加前导 0。
• 外部函数：
  • RegExReplace(...)- 与内部函数的结果一起工作
  • "0([0-9]{2})"- 查找后跟两位数字的 0（= 不需要前导 0 的情况）
  • "$1"- 仅保留两位数字，删除前导 0（仅限于上一步中匹配的情况）

你也可以在网上看到更多关于正则表达式的解释：

• 第一步：https://regex101.com/r/4W4dWw/1
• 第二步：https://regex101.com/r/mA8lGs/1

注意：我与该插件没有任何关联，只是使用它，因为它使我的生活更轻松。

更新

您可以将此公式用于 13 个字符的代码：
=RegExReplace(RegExReplace(A3,".*-([A-Z])-[A-Z]([0-9]).*-([A-Z])-U([0-9]+)-S([0-9]+)-P([0-9]+)","$1$2-$3-0$4-0$5-0$6"),"0([0-9]{2})","$1")

Answer 2

相当丑陋，
但您可以通过以下方式实现它，假设您的工作数据在 A 列中：

=TEXT(LEFT(RIGHT(A1,LEN(A1)-FIND("U",A1,1)),FIND("-",RIGHT(A1,LEN(A1)-FIND("U",A1,1)),1)-1),"00")&"-"&TEXT(MID(RIGHT(A1,LEN(A1)-FIND("U",A1,1)),FIND("-",RIGHT(A1,LEN(A1)-FIND("U",A1,1)),1)+2,(FIND("P",RIGHT(A1,LEN(A1)-FIND("U",A1,1)),1)-2-FIND("S",RIGHT(A1,LEN(A1)-FIND("U",A1,1)),1))),"00")&"-"&TEXT(RIGHT(RIGHT(A1,LEN(A1)-FIND("U",A1,1)),LEN(RIGHT(A1,LEN(A1)-FIND("U",A1,1)))-FIND("P",RIGHT(A1,LEN(A1)-FIND("U",A1,1)),1)),"00")