Excel 在公式栏中显示前导零 [英] Excel show leading zero in formula bar

问题描述

我有一个没有 0 的数据列表，所以我尝试了
单元格格式->自定义
添加一个前导零，它的工作原理.

I have a list of data without 0, so I tried
Format Cells->Custom
to add a leading zero and it works.

但是当我单击每个单元格时，公式栏中显示的数据仍然没有前导零.例如，在我自定义格式后的 excel 文件中:
0112244555

But when I click every single cell, the data shown in formula bar still didn't have the leading zero. For example, in my excel file after custom formatting:
0112244555

但是在公式栏中:
112244555

当我点击每个数据时，有没有什么方法可以显示带有前导零的数据?

Is there any way to display data with leading zeros as well when I click each of them?

推荐答案

删除前导零是 Excel 中的默认行为.

Removing leading zeroes is default behaviour in Excel.

您使用自定义格式的解决方法是显示前导零的标准

Your work-around to use a Custom format is standard to show leading zeroes

如果你想真正嵌入它们，那么你需要添加一个撇号即在 A1
'012
将显示
012

If you want to actually embed them then you will need to add an apostrophe ie in A1
'012
will display
012

作为文本 - 尽管您仍然可以在此单元格上执行代数操作，就好像它是作为数字输入的一样
12

as text - although you can still perform algebraic manipulation on this cell as if it was entered as a numeric
12

代码解决方案

此代码将:

• 仅在当前选择的数字常量单元格上运行(即忽略空格、文本、公式)
• 将在撇号后面添加两个前导零

因此，如果您在下面的 A 列上运行代码，结果将是 C 列中显示的更新单元格(仅用于演示，实际更新发生在 A1、A4 和 A5)

So if you ran the code on column A below, the result would be the updated cells shown in column C (for demonstration only, the actual updates occur in A1,A4 and A5)

改变这一行
strRep = "'00"
改变前导零的数量

Change this line
strRep = "'00"
to change the amount of leading zeroes

'按 Alt + F11 打开 Visual Basic 编辑器 (VBE)
'来自菜单，选择插入模块.
'将代码粘贴到右侧代码中窗口.
'按 Alt + F11 关闭 VBE
'在 Xl2003 中转到工具......宏...宏并双击 AddLeadingZeros

'Press Alt + F11 to open the Visual Basic Editor (VBE)
'From the Menu, choose Insert-Module.
'Paste the code into the right-hand code window.
'Press Alt + F11 to close the VBE
'In Xl2003 Goto Tools … Macro … Macros and double-click AddLeadingZeros

Sub AddLeadingZeros()
    Dim rng1 As Range
    Dim rngArea As Range
    Dim strRep As String
    Dim lngRow As Long
    Dim lngCol As Long
    Dim lngCalc As Long
    Dim X()

    strRep = "'00"

    On Error Resume Next
    'Set rng1 = Application.InputBox("Select range for the replacement of leading zeros", "User select", Selection.Address, , , , , 8)
    Set rng1 = Selection.SpecialCells(xlConstants, xlNumbers)
    If rng1 Is Nothing Then Exit Sub
    On Error GoTo 0

   'Speed up the code by turning off screenupdating and setting calculation to manual
   'Disable any code events that may occur when writing to cells
    With Application
        lngCalc = .Calculation
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With

    'Test each area in the user selected range

    'Non contiguous range areas are common when using SpecialCells to define specific cell types to work on
    For Each rngArea In rng1.Areas
        'The most common outcome is used for the True outcome to optimise code speed
        If rngArea.Cells.Count > 1 Then
           'If there is more than once cell then set the variant array to the dimensions of the range area
           'Using Value2 provides a useful speed improvement over Value. On my testing it was 2% on blank cells, up to 10% on non-blanks
            X = rngArea.Value2
            For lngRow = 1 To rngArea.Rows.Count
                For lngCol = 1 To rngArea.Columns.Count
                    'replace the leading zeroes
                    X(lngRow, lngCol) = strRep & X(lngRow, lngCol)
                Next lngCol
            Next lngRow
            'Dump the updated array sans leading zeroes back over the initial range
            rngArea.Value2 = X
        Else
            'caters for a single cell range area. No variant array required
            rngArea.Value = strRep & rngArea.Value2
        End If
    Next rngArea

    'cleanup the Application settings
    With Application
        .ScreenUpdating = True
        .Calculation = lngCalc
        .EnableEvents = True
    End With

End Sub

这篇关于Excel 在公式栏中显示前导零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！