如何限制单元格只输入数字 [英] How to restrict a cell to input only numbers

问题描述

我有一个excel文件，验证一列只能输入数字，但它的格式就好像数字少于18，将会添加前导零。但是现在第15位之后的数字将转换为0，例如：002130021300020789，九个更改为0.将列转换为文本后，其接受但我无法添加前导零，并且无法限制

I have an excel file, validating one column to enter numbers only but it is formatted as if the number is less than 18 the leading zeros will be added. But now the number after 15th digit it will be converting to 0 ex: "002130021300020789", the nine is changed as 0. After converting the column to text, its accepting but i am not able to add the leading zeros and not able to restrict to input onlu numbers.

欣赏任何帮助..提前感谢。

Appreciate any help.. thanks in advance.

推荐答案

MS文章：Excel遵循 IEEE 754规范关于如何存储和计算浮点数。因此，Excel只能存储一个数字中的15位有效数字，并将第15位之后的数字更改为零。

MS Article: Excel follows the IEEE 754 specification on how to store and calculate floating-point numbers. Excel therefore stores only 15 significant digits in a number, and changes digits after the fifteenth place to zeroes.

要获取数字格式并确保用户仅输入你可以做这个数字我假设您正在验证范围A1中的文本。请修改适用的

To get your number formatting and to also ensure that the user enters only numbers you can do this. I am assuming that you are validating the text in Range A1. Please amend as applicable.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa

    Application.EnableEvents = False

    If Not Intersect(Target, Range("A1")) Is Nothing Then
        '~~> If entered text is not a number then erase input
        If Not IsNumeric(Range("A1").Value) Then
            MsgBox "invalid Input"
            Application.Undo
            GoTo LetsContinue
        End If

        Range("A1").Value = "'" & Format(Range("A1").Value, "000000000000000000")
    End If

LetsContinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

FOLLOWUP

如果您正在复制和粘贴，则您必须首先将Range G11：G65536手动格式化为TEXT ，然后使用此代码

If you are copying and pasting then you will have to first format the Range G11:G65536 manually as TEXT and then use this code

SNAPSHOT（粘贴数值时）

SNAPSHOT（粘贴非数值时）

代码

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa

    Dim cl As Range

    Application.EnableEvents = False

    If Not Intersect(Target, Range("G11:G" & Rows.Count)) Is Nothing Then
        For Each cl In Target.Cells
            '~~> If entered text is not a number then erase input
            If Not IsNumeric(cl.Value) Then
                MsgBox "invalid Input"
                Application.Undo
                GoTo LetsContinue
            End If

            cl.Value = "'" & Format(cl.Value, "000000000000000000")
        Next
    End If

LetsContinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

这篇关于如何限制单元格只输入数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！