Force format

nomad

Thread Starter
Joined
Sep 30, 1999
Messages
407
I would like to format a cell so the exact format has to be entered otherwise it produces an error. the format is (123) 456-7890. Maybe i should embellish (###) ###-####. nothing else accepted. bracets must be typed in also. can anyone help?
thanx
 

Xsage

Dave
Joined
Jan 8, 2016
Messages
300
Code:
=IF(LEN($A1)=10,TEXT($A1,"(###) ###-####"),"ERROR")
This requires 2 columns to work, the first column - Column A is where the source number goes, e.g 1234657890
The second column, where the formula goes is where you will see the formatted result and or any errors.

Basically the formula says, if the length of the string in A# is equal to 10, format it as specified, otherwise write "ERROR" into the cell.
 

nomad

Thread Starter
Joined
Sep 30, 1999
Messages
407
I would like to use data validation somehow to do this. would that be possible.

thanks for you help
 

Xsage

Dave
Joined
Jan 8, 2016
Messages
300
Okay, sorry this took a while, I was doing it between my day job :p

Code:
=AND(IF(LEFT(B97,1)="(",TRUE,FALSE),ISNUMBER(NUMBERVALUE(MID(B97,2,3))),IF(MID(B97,5,1)=")",TRUE,FALSE), IF(MID(B97,6,1)=" ",TRUE,FALSE),ISNUMBER(NUMBERVALUE(MID(B97,7,3))),IF(MID(B97,10,1)="-",TRUE,FALSE),ISNUMBER(NUMBERVALUE(MID(B97,11,4))),IF(LEN(B97)=14,TRUE,FALSE))
Above is the validation code required to match the exact format specified. Unfortunately you can't plug it straight into the custom datavalidation field, because the length of the formula exceeds 255 characters, which is the maximum the cutsom data validation field allows. So you have to put that formula on your sheet somewhere out of the way and then in the data validation field, just reference the cell holding the formula, for example:

Code:
=C97
You don't need to worry too much about the cell references in the top formula as datavalidation will automatically and dynamically replace the references for each cell you apply the validation to.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top