Google Sheets函数需求：仅移除括号并在移除时将数值转为负数

最终函数 #

=IF(
  AND(ISNUMBER(SEARCH("(", 'Cash Flow'!B17)), ISNUMBER(SEARCH(")", 'Cash Flow'!B17))),
  -1 * IF(
    RIGHT(REGEXREPLACE('Cash Flow'!B17, "[()]", ""), 1) = "B",
    LEFT(REGEXREPLACE('Cash Flow'!B17, "[()]", ""), LEN(REGEXREPLACE('Cash Flow'!B17, "[()]", "")) - 1) * 1000000000,
    IF(
      RIGHT(REGEXREPLACE('Cash Flow'!B17, "[()]", ""), 1) = "M",
      LEFT(REGEXREPLACE('Cash Flow'!B17, "[()]", ""), LEN(REGEXREPLACE('Cash Flow'!B17, "[()]", "")) - 1) * 1000000,
      REGEXREPLACE('Cash Flow'!B17, "[()]", "")
    )
  ),
  IF(
    RIGHT('Cash Flow'!B17, 1) = "B",
    LEFT('Cash Flow'!B17, LEN('Cash Flow'!B17) - 1) * 1000000000,
    IF(
      RIGHT('Cash Flow'!B17, 1) = "M",
      LEFT('Cash Flow'!B17, LEN('Cash Flow'!B17) - 1) * 1000000,
      'Cash Flow'!B17
    )
  )
)

逻辑说明 #

1. 括号检测：通过AND(ISNUMBER(SEARCH("(", ...)), ISNUMBER(SEARCH(")", ...)))判断单元格是否同时包含左右括号
2. 带括号的处理：
   • 用REGEXREPLACE(..., "[()]", "")移除所有括号
   • 将清理后的内容代入原有单位转换逻辑（处理B/M后缀的数值放大）
   • 最后乘以-1实现负数转换
3. 无括号的处理：直接执行你提供的原有单位转换逻辑

简化优化（可选） #

用LET函数封装重复逻辑，提升可读性和维护性：

=LET(
  original_val, 'Cash Flow'!B17,
  has_parentheses, AND(ISNUMBER(SEARCH("(", original_val)), ISNUMBER(SEARCH(")", original_val))),
  cleaned_val, IF(has_parentheses, REGEXREPLACE(original_val, "[()]", ""), original_val),
  sign_multiplier, IF(has_parentheses, -1, 1),
  sign_multiplier * IF(
    RIGHT(cleaned_val, 1) = "B",
    LEFT(cleaned_val, LEN(cleaned_val) - 1) * 1000000000,
    IF(
      RIGHT(cleaned_val, 1) = "M",
      LEFT(cleaned_val, LEN(cleaned_val) - 1) * 1000000,
      cleaned_val
    )
  )
)

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