Data validation - numbers as text

Dear Fellas,

I would very much appreciate your help on the following:

Would anyone know how to enable data validation in order to enforce putting numbers as texts. So I want user to only be able to input '123123 and not 123123.

I was thinking 'custom' in 'data validation' and I have tried formula = " ' " & " * " [no spaces].

If you have any suggestions please let us know.

ps. Where this is used? I have a sumproduct function that validates certain values and it is not working if values are values and not texts (=sumproduct(A1:A10,--(B1:B10=C1)), where C1 is ['123123].

Cheers!

You can choose Allow:Custom and try this formula: =ISTEXT(A2)=TRUE

You can make the adjustment in the formula..

Originally Posted by gutkinma

I have a sumproduct function that validates certain values and it is not working if values are values and not texts (=sumproduct(A1:A10,--(B1:B10=C1)), where C1 is ['123123].

Cheers!

=sumproduct(A1:A10,--(B1:B10&""=C1))
or
=sumproduct(A1:A10,--(B1:B10=C1+0))

Thank you all,

AlKey - good call, but I want a fool-proof solution.

tchock, Jonmo1 - thanks, all works perfectly.

Many thanks!!!