☰

用于去除数字前后缀的 Excel 公式

tag-icon • tag-icon microsoft-excel
用于去除数字前后缀的 Excel 公式

前几天，我的电子表格 ($X/hr, Y hrs) 中有一些时间表信息，我想计算花费的金额。最后我直接删除了“/hr”、“hrs”和“$”字符，但是有没有办法在公式中将这些单元格直接转换为浮点数来执行计算？

=B$3 * B$4

我尝试了 VALUE，但没有作用。

答案1

尝试一下这个小自定义函数

Public Function NumberPart(s As String) As Double
    Dim s2 As String, i As Long, L As Long, CH As String
    s2 = ""
    L = Len(s)
    For i = 1 To L
        CH = Mid(s, i, 1)
        If CH Like "[0-9]" Or CH = "." Then
            s2 = s2 & CH
        End If
    Next i
    NumberPart = CDbl(s2)
End Function

用户定义函数 (UDF) 非常容易安装和使用：

  1. ALT-F11 打开 VBE 窗口
  2. ALT-I ALT-M 打开新模块
  3. 粘贴内容并关闭 VBE 窗口

如果您保存工作簿，UDF 将随之保存。如果您使用的是 2003 以后的 Excel 版本，则必须将文件另存为.xlsm而不是.xlsx

要删除 UDF：

  1. 调出如上所示的 VBE 窗口
  2. 清除代码
  3. 关闭 VBE 窗口

要从 Excel 使用 UDF：

=数字部分（A1）

要了解有关宏的更多信息，请参阅：

http://www.mvps.org/dmcritchie/excel/getstarted.htm

和

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

和

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

有关 UDF 的详细信息

必须启用宏才能使其工作！

答案2

尽管上述答案有效并且有据可查，但我对此有两点反对意见：

  • 如果输入包含非预期的数值（例如，尝试：“12 hrs1”或“$ 12 / 1 hr”或者当您的区域设置使用“，”而不是“。”作为小数分隔符），则会给出错误的输出。
  • 如果可能的话，我会远离 Visual Basic for Applications (VBA) 和 UDF；特别是如果可以使用以下本机 Excel 函数实现相同的功能：
    • LEFT：将字符串左侧的长度控制在一定范围内（用于截断“/hr”）。
    • VALUE：将文本转换为值（用于将字符串中剩余的值转换为值）。
    • FIND：在另一个字符串中搜索字符或字符串（用于在“/hr”中搜索‘/’）。
    • SUBSTITUTE：更改另一个字符串中出现的所有字符串（用于删除“HRS”）。
    • UPPER：将所有字符转换为大写（用于将“Hrs”和“hrs”等更改为“HRS”）。

如果提到的值在 A 列（每小时价格）和 B 列（小时数），则从第 1 行（无标题行）的数据开始，然后在 Excel 中输入以下公式：

  • C1：=VALUE(LEFT(A2;FIND("/";A1;1)-1))并根据需要复制粘贴下来。
  • D1：=VALUE(SUBSTITUTE(UPPER(B1);"HRS";"")) 并根据需要复制粘贴下来。

这应该可以解决问题。我想没什么太复杂的。

答案3

另一种方法是使用 VBA 与 Regex 和试穿模式。
例如，该\D模式匹配所有非数字（0-9）的内容。

  1. 将此代码插入到 VBA 中模块( ALT+ F11)。从 Excel 调用时，UDF 需要 2 个参数：输入单元格和要检查的 RegEx 模式。然后，代码会删除与给定模式匹配的每个字符。

    Function Remove(objCell As Range, strPattern As String)

    Dim RegEx As Object
    Set RegEx = CreateObject("VBScript.RegExp")
    RegEx.Global = True
    RegEx.Pattern = strPattern
    Remove = RegEx.Replace(objCell.Value, "")

End Function

  2. 返回 Excel，按照此语法将公式写入新单元格

    =Remove( <cellToCheck> ,"<regExPattern>")

我采用你的例子来将两个“非数字”单元格相乘：$50/hr * 8 hrs = 400

在此处输入图片描述

但是，如果一个单元格中有多个（非预期的）数值，则此方法无效，例如$50/hr2

相关内容