如何将含非时长内容的时长文本列转换为分钟数

问题场景 #

你有一列包含时长文本的单元格，部分内容为无效值（空、分隔符、标题等），需要仅将有效时长（如14h 58m 30s、1d 7h 27m）转换为分钟数。当前公式需要先用正则提取带单位的片段，再用REGEXREPLACE去掉单位，希望找到更高效的直接提取数值的方法。

示例数据（A列）：

优化思路：直接捕获数值的正则表达式 #

核心是修改REGEXEXTRACT的正则规则，直接捕获天、时、分、秒对应的数字，不需要先提取带单位的字符串再替换。这样得到的数组本身就是纯数值（空则代表该时长单位不存在），可以直接参与后续的分钟转换计算。

优化后的完整公式 #

=LET(
    ind_last_row, 60,
    time, OFFSET(A1, 0, 0, ind_last_row, 1),
    time_2_num_arr, LAMBDA(time, REGEXEXTRACT(time, "(?:(\d+)d)?\s*(?:(\d+)h)?\s*(?:(\d+)m)?\s*(?:(\d+)s)?")),
    time_arr_2_min, LAMBDA(arr, SUMPRODUCT(ARRAYFORMULA(IFERROR(--arr, 0)), {1440, 60, 1, 1/60})),
    is_valid_time, LAMBDA(arr, NOT(AND(ARRAYFORMULA(IFERROR(--arr, 0)=0)))),
    MAP(time, LAMBDA(t, LET(
        num_arr, time_2_num_arr(t),
        IF(is_valid_time(num_arr), time_arr_2_min(num_arr), "")
    )))
)

关键优化点说明 #

1. 正则表达式调整
   原正则"(\d*d)?\s*(\d*h)?\s*(\d*m)?\s*(\d*s)?"会捕获带单位的字符串（如14h），优化后的"(?:(\d+)d)?\s*(?:(\d+)h)?\s*(?:(\d+)m)?\s*(?:(\d+)s)?"：

   • (?:...)是非捕获组，用来包裹数字+单位的结构，只捕获里面的数字部分
   • (\d+)直接捕获纯数字，确保提取结果不带单位
   • 每个捕获组对应天、时、分、秒的数字，不存在的单位会返回空值

2. 省去REGEXREPLACE步骤
   提取到的num_arr是纯数字（或空），用IFERROR(--arr, 0)把空值转为0，直接和转换系数{1440, 60, 1, 1/60}做SUMPRODUCT计算，无需额外替换操作。

3. 有效时长判断优化
   原nomatch判断所有捕获组为空，优化后的is_valid_time判断是否至少有一个捕获组的数值不为0，避免把有效时长误判为无效。

效果验证 #

• 对14h 58m 30s，提取的数值数组是{"", "14", "58", "30"}，计算得14*60 + 58 + 30/60 = 898.5分钟
• 对1d 7h 27m，数组是{"1", "7", "27", ""}，计算得1*1440 +7*60 +27 = 1887分钟
• 空值、—、Header这类无效内容会返回空

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