International and United States custom cell formatting?

**vamosj** · 06-27-2013, 10:37 AM

Not sure if being idiotic or just not possible, but I am trying to insert custom formatting into a cell so when I enter a set of 9 numbers for phone numbers in the U.S. and 14 numbers for international phone numbers, it will automatically display it in the correct format. Reason for the 0's on the international is because country codes often start with a 0, so I need to ensure it isn't removed from the number. Any ideas on how one might be able to do this would be appreciative, this one is stumping me.

United States Format: (###) ###-####
International Format: 000-000-0000-0000

**XOR LX** · 06-27-2013, 11:01 AM

Hi Janos,

To my knowledge you can't do this with number formats. You could transfer the results via a formula into another column, e.g.

=IF(LEN(A1)=9,"("&LEFT(A1,3)&") "&MID(A1,4,3)&"-"&RIGHT(A1,4),LEFT(A1,3)&"-"&MID(A1,5,3)&"-"&MID(A1,9,4)&"-"&RIGHT(A1,4))

Perhaps (if you haven't already done so) this could be combined with some data validation on the input cells to prevent entries of other than 9 or 14 characters.

Regards

**vamosj** · 06-27-2013, 11:40 AM

I may just have to do a conditional macro that will check the value and change it as required. I was just trying to stay away from that as it is the same area I needed for lookups of contacts, new contacts, and updating contacts so I would have to go back through all my current macros and ensure that the hyphens and parenthesis are removed from the values.