CODEWITHSUNDEEP
excelvalidation
Rocket
Rocket

Reputation: 1093

Excel - Format cell as TEXT, but validate as number?

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

Upvotes: 0

Views: 736

Answers (1)

cjc
cjc

Reputation: 751

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.

Upvotes: 2

Related Questions