Data Validation - Only allowing numbers and "+" si

[supergoat]

Hi,

I need to only allow numbers a plus sign '+', and spaces into a cell. This is so that it can be used for international telephone numbers e.g. +44 666 8745.
Any ideas how I restrict the values?

Many Thanks

 

[Aladin Akyurek]

Re: Data Validation - Only allowing numbers and "+&quot

Hi,

I need to only allow numbers a plus sign '+', and spaces into a cell. This is so that it can be used for international telephone numbers e.g. +44 666 8745.
Any ideas how I restrict the values?

Many Thanks

You need to pre-format the cells you want to data validate, then set up custom data validation with as formula:

=ISNUMBER(SUBSTITUTE(D1," ","")+0)

where D1 is such a cell.

 

[supergoat]

Thanks,

But i can't really follow it.
I have just one cell that will contain both '+' ad numbers.
What do I need to put in the format cells - custom format - 'type' box?
Does D! refer to my cell where data entry will take place?

 

[Aladin Akyurek]

Thanks,

But i can't really follow it.
I have just one cell that will contain both '+' ad numbers.
What do I need to put in the format cells - custom format - 'type' box?
Does D! refer to my cell where data entry will take place?

Lets say that you want to data validate D1:D10...

Select D1:D10.
Activate Cells|Format and choose Text.
Activate Data|Validation.
Choose Custom for Allow.
Enter in the formula box:

=ISNUMBER(SUBSTITUTE(D1," ","")+0)

Click OK.

Start entering phone numbers with + and spaces in D1 onwards.

 

[supergoat]

Nice One. I get you now

Thank you very much.