I want users to enter numbers in a cell, without any leading zero's being removed or the number being changed to a formula.

If I set the cell as General, I can then use Data Validation and set that as Whole Number, but any leading zero's are removed. eg: 000123654 is returned as 123654

If I set the cell as Text, then the number is shown as 000123654, but validation fails as it's Text not a number.

Is there any way to do this ?

Thanks

CodePudding user response：

If you set the validation criteria to Custom and use the formula =ISNUMBER(NUMBERVALUE(A1)) (swap A1 for the actual reference) that will force the input to be numeric while still allowing you to set the cell as Text.

NUMBERVALUE will convert the text to a number or return an error if it's not a number. ISNUMBER will return true if it's input is a number, which in this case means NUMBERVALUE returned number meaning the input was actually numeric.