Data validation to enforce number format entry

  • I need to use data validation to enforce input of a number in the format ### ### #### (So, a ten figure number with a single space after digits 3 and 6)

    I cannot use number formatting as this is not available in the online version of excel that my employer uses.

    Please could someone help me with a formula for this?

  • I'm pretty sure that you cannot force the number format with Data Validation, you can force the user to enter a number and the length to be 10 digits.

    What you are asking for is a Custom Number Format probably for a telephone number.

  • Forcing the user was what I was looking for.

    It's because custom number formatting is, for some reason, not available when using excel online.

    However, I have discovered that, although customised number fomatting is not available, I can import a spreadsheet created offline and the customised formatting does transfer. Which will allow my colleagues to continue with their lazy inputs without messing up my spreadsheet. Which works for now.

    But I'd still appreciate a formula if anyone knows how to create it.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!