如何在Excel中删除前导零

您想学习如何在Excel中删除前导零吗？本快速 Excel 教程包含一些您可以使用的最佳技巧。

当您以“数字”或“常规”格式输入数据时，Microsoft Excel 会删除前导零。但是，您经常会使用从第三方软件或数据库导入的数据集，这时您可能会遇到非零值前有前导零的问题。

无论您是想让数据集保持一致、提高其可读性，还是减少包含大量冗余零的超大工作簿的 Excel 文件大小，我都能满足您的需求。以下是轻松去除 Excel 中前导零的不同方法。

使用转换为数字删除前导零

在上述数据集中，销售数据已从第三方商业数据库导入。销售值显示为带有前导零的文本字符串。但是，作为销售数字，这不太可读，而是随机数。

默认情况下，自 Excel 2013 起，您应该在包含此类数值的所有单元格上看到公式错误检查图标。

要删除这些前导零，请突出显示目标列中的所有单元格。

单击该批次第一个单元格上的黄色警告标志。

这将打开一个包含多个选项的上下文菜单。在那里，单击“转换为数字”选项。

Excel 应将突出显示的数据集重新格式化为数字并删除前导零。

现在，您可以单击“主页”选项卡的“数字”下拉菜单中的“货币”选项，将数据集适当地重新格式化为$值。

在 Excel 中乘以 1 删除前导零

将文本值转换为数字并删除前导零的另一种快速方法是在 Excel 中将整个数据集乘以1 。

但是，要将数据集乘以 1，您无需在工作表中创建任何帮助列。相反，您可以使用“选择性粘贴”对话框快速乘以并转换数据集。

首先，在工作表的任意空白单元格中输入数字格式的数字1 。

现在，按+从其单元格复制数字1。CtrlC

转到包含以零开头的文本字符串的目标列。突出显示整个数据集并按Ctrl+ Alt+V调出“选择性粘贴”对话框。

在“选择性粘贴”对话框中，单击“操作”部分下方的“乘法”选项，然后单击“确定”按钮。

现在您将看到 Excel 已将输入数据集转换为减去前导零的数字。

使用查找和替换删除前导零

查找和替换工具也可以帮助您删除 Excel 中的前导零。但是，此方法仅适用于那些仅包含任意数量的前导零但前导零后都是非零数字的数据集。

例如，客户/帐号、产品代码/SKU、序列号等数据集通常只包含前导零，非零空间内不包含零。

确定数据集的非零部分内没有任何零后，按Ctrl+H调出“查找和替换”对话框。

在“查找内容”字段中输入*0。保留“替换为”字段为空。现在，点击“全部替换”按钮。

Excel 将删除前导零并将数据集保留为文本字符串。

如果数据集的非零部分也包含零，则不要使用此方法。

使用文本到列删除前导零

文本分列功能可让您从文本字符串中删除前导零。此外，您还可以修改其功能以获取输出的数字格式或文本格式。

例如，如果输入数据集代表货币值，请突出显示该数据集并从“主页”选项卡中的“数字”下拉菜单切换到“货币”格式。

现在，转到“数据”选项卡并单击数据工具块内的“文本到列”命令按钮。

现在您将看到将文本转换为列向导。在步骤 1、步骤 2和步骤 3上单击下一步。

确保列数据格式为默认格式，即“General”。现在，单击“完成”按钮。

这样做会删除所有前导零，并将文本字符串转换为具有货币格式的数字。您还会看到$符号。此外，值最多应有两位小数。

如果输入的数据集不是货币值，并且您不想将格式更改为数字形式的文本，只需在工作表中突出显示数据集，然后从“主页”选项卡中的“数字格式”下拉菜单中将其更改为文本格式。

对“文本到列”工具重复前面提到的步骤。Excel 将删除前导零，但不会更改源格式。

使用 VALUE 函数删除前导零

由于VALUE函数会将所有表示数值的文本字符串转换为数字，因此您可以使用它从数据集中删除前导零。Excel 具有内置编程功能，可以从数字格式的值中删除前导零，因此该方法可以顺利运行。

首先，导航到目标数据集并突出显示源数据集第一个单元格旁边的空单元格。

在空单元格中输入以下公式。您必须根据自己的数据集修改单元格引用。

=VALUE(C2)

点击Enter可将选定单元格中的输入转换为数字。

现在，将填充柄从第一个单元格向下拖动，直到源数据集的相邻单元格中存在相关数据。

您应该获得不带前导零的值。请注意，输出数据集采用数字格式。

现在，如果您只需要转换的值而不是底层公式，请复制刚刚创建的新列（列标题单元格除外）。

Ctrl然后，按+ Alt+将复制的内容粘贴到源数据集列V中。在出现的“选择性粘贴”对话框中，选择“值”，然后单击“确定”按钮。

现在，您可以删除之前创建的公式列。

使用 TEXT 函数删除前导零

假设您想从输入数据集中删除零。此外，由于数据集代表销售数字，您希望将输出值转换为货币格式。在这种情况下，您可以使用以下TEXT公式：

=TEXT(C2,"$#,##0.00")

在上面的公式中，只需要改变引用单元格C2。

您必须在与源数据集相邻的第一个单元格中输入公式。然后，使用填充柄将相同的公式应用于其余单元格。

或者，如果您只希望删除前导零并保留输入数据集的文本格式，则可以使用以下TEXT公式：

TEXT(C2,"0")

通过使用此TEXT函数，您可以保留输入数据集的文本格式，而不会在所有单元格中出现那些烦人的公式错误检查图标。

使用 Power Query 删除 Excel 中的前导零

从外部数据库导入带有前导零的大型数据集时，可以使用Excel 的Power Query工具清除数据中不必要的零。您也可以对工作表数据集遵循此方法。

在当前练习中，我将使用工作表中带有前导零的数据集。为此，我将突出显示工作表中的输入数据集，然后单击Excel 功能区菜单中“数据”选项卡的“获取和转换数据”块中的“来自表/范围”命令。

但是，如果您有兴趣从数据库导入数据，您可以转到空白工作表上的“数据”选项卡。

单击获取和转换数据命令块内的获取数据命令以打开数据导入上下文菜单。

将鼠标光标悬停在“来自数据库”菜单选项上，然后从溢出上下文菜单中选择外部数据库源。例如，您可以选择“来自数据库” > “来自 SQL Server 数据库”。

无论您从工作表导入内部数据集还是从数据库导入外部数据集，您现在都可以在Power Query工具中找到该数据集以进行进一步的数据操作。

您应该看到Power Query在删除前导零后已自动将输入数据集转换为数字格式。

现在，如果您的数据集代表货币价值，并且您希望相应地显示它，请单击Power Query中的“销售额”列，然后转到“转换”选项卡。

单击任意列块内的数据类型选项，然后在显示的上下文菜单中选择货币。

Power Query 会将这些值转换为货币。

或者，如果您希望在删除前导零后保留输入数据集的文本格式，请在Power Query中的“转换”选项卡的“数据类型”下拉菜单中选择“文本”。

现在您已经转换了输入数据列，是时候将数据集从 Power Query 导出到 Excel 工作表了。

单击“文件”选项卡，然后从上下文菜单中选择“关闭并加载到”选项。

源工作表上将显示“导入数据”对话框。在此处，选择“现有工作表”选项，并突出显示不与源数据集重叠的单元格范围作为目标。

单击“确定”完成数据导出过程。

现在，删除工作表的旧表格。

要将导入的数据集转换为正常范围，请右键单击表格，然后从上下文菜单的表格菜单中选择“转换为范围” 。

就这样！您已成功使用 Power Query 删除了前导零。

使用 Excel VBA 删除 Excel 中的前导零

如果您是经验丰富的 Excel 用户或愿意探索 Excel 中的 VBA 编程，则可以使用VBA 宏删除前导零。

以下脚本允许您选择源数据集、选择目标单元格范围，最后选择输出数据格式。所有操作都可以使用鼠标光标直观地完成，无需编写任何代码。

Sub RemoveLeadingZeroes()
    Dim rngInput As Range
    Dim rngOutput As Range
    Dim cell As Range
    Dim formatType As String

    ' Prompt user to select input range
    On Error Resume Next
    Set rngInput = Application.InputBox("Select the range with leading zeroes", Type:=8)
    On Error GoTo 0

    ' Prompt user to select output range
    On Error Resume Next
    Set rngOutput = Application.InputBox("Select the destination range", Type:=8)
    On Error GoTo 0

    ' Prompt user to select formatting type
    formatType = InputBox("Select the output data formatting: " & vbCrLf & _
                          "1. Keep as text strings" & vbCrLf & _
                          "2. Convert to number" & vbCrLf & _
                          "3. Convert to currency")

    ' Validate formatType
    If formatType  "1" And formatType  "2" And formatType  "3" Then
        MsgBox "Invalid format type. Please enter 1, 2, or 3."
        Exit Sub
    End If

    ' Process each cell in the input range
    For Each cell In rngInput
        Select Case formatType
            Case "1"
                rngOutput.Value = "'" & Trim(Str(cell.Value))
            Case "2"
                rngOutput.Value = Val(Trim(Str(cell.Value)))
            Case "3"
                rngOutput.Value = Format(Val(Trim(Str(cell.Value))), "$#,##0.00")
        End Select
        Set rngOutput = rngOutput.Offset(1, 0)
    Next cell
End Sub

从选择输入数据集到选择输出数据集的格式，每一步都会有一个输入框，其中包含详细说明。您只需使用鼠标或键盘输入选择所需的单元格范围和选项即可。

要使用此脚本开发宏，请按照以下文章操作：

使用 Office 脚本删除 Excel 中的前导零

如果您希望在 Excel for the web 中自动执行前导零删除过程，则可以使用Office 脚本。但是，此功能也可在 Excel for the Microsoft 365 桌面应用程序中使用。

转到“自动化”选项卡，然后单击“新建脚本”按钮。在打开的代码编辑器中，复制并粘贴以下 Office 脚本：

function main(workbook: ExcelScript.Workbook) {
    // Get the current worksheet
    let worksheet = workbook.getActiveWorksheet();
    
    // Get the selected range
    let range = workbook.getSelectedRange();
    
    // Get the values in the range
    let values = range.getValues();
    
    // Iterate over each cell in the range
    for (let i = 0; i

单击保存脚本按钮。

现在，突出显示输入数据集的单元格范围，然后单击代码编辑器控制台中的运行按钮。

Excel 将用货币格式替换突出显示的数据集并删除前导零。

如果您希望保留文本格式的输出数据集，请使用以下脚本：

function main(workbook: ExcelScript.Workbook) {
    // Get the current worksheet
    let worksheet = workbook.getActiveWorksheet();

    // Get the selected range
    let range = workbook.getSelectedRange();

    // Get the values in the range
    let values = range.getValues();

    // Iterate over each cell in the range
    for (let i = 0; i

结论

现在您知道了如何在各种情况下删除 Excel 中的前导零。您可能会发现上述所有方法都适用于不同情况。例如，当您导入外部数据集并想要清除前导零时，Power Query 就很合适。

除了手动方法之外，我还解释了涉及 Excel VBA 和 Office 脚本的自动化技术，以防您需要完全自动化该过程。

在迄今为止解释的所有方法中，您最喜欢哪一种方法？您知道比这里提到的更好的技术吗？请在评论框中写几行来分享您的想法或反馈。

如何在Excel中删除前导零