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

问题描述

我有一个没有0的数据列表，所以我试过

格式化单元格>自定义

添加一个领先的零，它的作品。

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

0112244555

但是在公式栏：

112244555

有没有办法用前导零显示数据那么当我点击他们的每一个？

解决方案

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

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

如果要实际嵌入它们，则需要添加撇号
即在A1

'012

将显示

012

作为文本 - 虽然您仍然可以对该单元格执行代数操作，就像将其作为数字输入一样

12

代码解决方案

此代码将：只能在当前选择中的数字常数单元格上运行（即忽略空白，文本，公式），p>

• ）


• 将在撇号后添加两个前导零。

所以如果你运行代码下面的列A，结果将是列C中显示的更新的单元格（仅供演示，实际更新发生在A1，A4和A5中）

更改此行

strRep ='00

更改前导零的数量

'按Alt + F11打开Visual Basic编辑器（VBE）

'从
菜单中选择插入模块。

'粘贴代码进入右边代码
窗口。

'按Alt + F11关闭VBE

'在Xl2003 Goto Tools ...
宏...宏并双击AddLeadingZeros

  Sub AddLeadingZeros（）
 Dim rng1 As Range 
 Dim rngArea As Range 
 Dim strRep As S tring 
 Dim lngRow As Long 
 Dim lngCol As Long 
 Dim lngCalc As Long 
 Dim X（）
 
 strRep ='00
 
 On Error Resume Next 
'设置rng1 = Application.InputBox（选择范围替换前导零，用户选择，Selection.Address，，，，，8）
设置rng1 = Selection.SpecialCells（xlConstants，xlNumbers）
如果rng1是Nothing然后退出Sub 
打开错误GoTo 0 
 
'通过关闭屏幕更新和设置来加快代码计算到手动
'禁用在写入单元格时可能发生的任何代码事件
应用程序
 lngCalc = .Calculation 
 .ScreenUpdating = False 
 .Calculation = xlCalculationManual 
 .EnableEvents = False 
结束
 
'测试用户选择范围内的每个区域
 
'使用SpecialCells定义时，非连续范围区域很常见特定的细胞类型工作on 
对于每个rngArea在rng1.Areas 
'最常见的结果是用于True结果来优化代码速度
如果rngArea.Cells.Count> 1然后
'如果有多个单元格，那么将变量数组设置为范围区域的尺寸
'使用Value2提供了相对于Value的有用的速度改进。在我的测试中，空白单元格是2％，在非空白单元上高达10％
 X = rngArea.Value2 
对于lngRow = 1到rngArea.Rows.Count 
对于lngCol = 1到rngArea.Columns.Count 
'替换前导零
 X（lngRow，lngCol）= strRep& X（lngRow，lngCol）
下一页lngCol 
下一页lngRow 
'将更新的数组转储到初始范围内的前导零点
 rngArea.Value2 = X 
 Else 
'适用于单个单元格区域。不需要变体数组
 rngArea.Value = strRep& rngArea.Value2 
结束如果
下一个rngArea 
 
'清理应用程序设置
应用程序
 .ScreenUpdating = True 
 .Calculation = lngCalc 
 .EnableEvents = True 
结束
 
 End Sub 
  

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

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

But in the formula bar:
112244555

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

解决方案

Removing leading zeroes is default behaviour in Excel.

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

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

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

Code Solution

This code will:

• run on only numeric constant cells in the current selection (ie ignoring blanks, text, formulae)
• will add two leading zeroes behind an apostrophe

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)

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

'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屋！