Geek asks and answers

How to limit a cell to enter only numbers

I have an excel file that checks one column for inputting numbers, but it is formatted as if the number were less than 18, leading zeros would be added. But now the number after the 15th digit will be converted to 0 ex: "002130021300020789", nine will be changed to 0. After converting the column to text, it is accepted, but I can not add leading zeros and can not limit to enter onlu numbers.

Appreciate any help .. thanks in advance.

+5
source share
2 answers

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

To get the number formatting, and also make sure that the user enters only the numbers, you can do this. I assume you are checking the text in Range A1. Change if 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

If you copy and paste, you need to first format the range G11: G65536 manually as TEXT , and then use this code

SNAPSHOT (when pasting numerical values)

enter image description here

SNAPSHOT (when pasting non-numeric values)

enter image description here

CODE

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
+2
source

First you can change the format of the cell in the text

Range("A1").NumberFormat = "@"

Then you can add zeros

cellvalue2 = Format(cellvalue1, "000000000000000")  // for numeric fields

                   or 
             = TEXT(cellvalue1,"000000000000000")    // for textfields
+1

More articles:


All Articles