Google Sheets中从产品编码提取指定数值用于计算的技术求助

Hey there, let's break down how to extract those three parameters from your product codes—this is actually a perfect use case for regex, once we leverage the fixed possible values of your third parameter!

问题分析 #

Your product code follows a consistent pattern: [前缀]-[参数1]U[参数2][参数3][后缀], where:

• 参数1 is a numeric value right after the -
• 参数2 is 2-3 digits, followed directly by...
• 参数3 which can only be 12 or 24—this is our key anchor point!

Since 参数3 has fixed, predictable values, we can use regex to match those first, which will automatically carve out 参数2 correctly, no matter if it's 2 or 3 digits.

解决方案：正则表达式匹配 #

Here's a regex pattern that will handle all valid cases:

^[A-Z]+-(\d+)U(\d+)(12|24)[A-Z]$

Let's break down what each part does:

• ^[A-Z]+-: Matches the prefix (like ASG-) at the start of the string
• (\d+): Captures 参数1 (e.g., 120) as a group
• U: Matches the literal separator U
• (\d+): Captures 参数2 (e.g., 96 or 220) as a group
• (12|24): Captures 参数3, restricted to only 12 or 24
• [A-Z]$: Matches the trailing suffix (like M) at the end of the string

代码示例（Python） #

Here's how you can implement this in Python to extract the parameters:

import re

def extract_parameters(code):
    pattern = r"^[A-Z]+-(\d+)U(\d+)(12|24)[A-Z]$"
    match = re.match(pattern, code)
    if match:
        return {
            "param1": match.group(1),
            "param2": match.group(2),
            "param3": match.group(3)
        }
    return None

# Test with your example code
code = "ASG-120U9624M"
params = extract_parameters(code)
if params:
    print(f"提取到的参数：\n参数1: {params['param1']}\n参数2: {params['param2']}\n参数3: {params['param3']}")
else:
    print("编码格式不符合要求，请检查输入！")

测试其他场景 #

This pattern works for 3-digit 参数2 too—try it with ASG-150U22012M:

• 参数1: 150
• 参数2: 220
• 参数3: 12

JavaScript 版本示例 #

If you're working in JavaScript, here's the equivalent implementation:

function extractParameters(code) {
    const pattern = /^[A-Z]+-(\d+)U(\d+)(12|24)[A-Z]$/;
    const match = code.match(pattern);
    if (match) {
        return {
            param1: match[1],
            param2: match[2],
            param3: match[3]
        };
    }
    return null;
}

// Test case
const code = "ASG-120U9624M";
const params = extractParameters(code);
if (params) {
    console.log(`提取到的参数：
参数1: ${params.param1}
参数2: ${params.param2}
参数3: ${params.param3}`);
} else {
    console.log("编码格式不符合要求，请检查输入！");
}

为什么这个方法有效 #

By anchoring our regex to the fixed possible values of 参数3 (12 or 24), we let the regex engine automatically figure out where 参数2 ends—no need to hardcode its length. This makes the solution flexible and robust for all valid code formats you mentioned.

内容的提问来源于stack exchange，提问作者Karolis Lozickas