I would like to construct a formula or custom number format (or VB code?)
that will do the following:

This would all occur within a single column.

If a five digit phone extension beginning with a 1 is entered (e.g., 12345),
I would like Excel to automatically add the area code and first two digits of
an extension. Example result: (212) 551-2345

If a five digit extension beginning with a 2 is entered (23456) I would like
the result to be (212) 662-3456. And if a five digit extension beginning
with a 3 is entered (34567), the the result should be (212) 773-4567. And so
on...

If a seven digit number is entered, Excel should add the area code - e.g.,
enter 5123456, result should be (212) 512-3456.

If a full telephone number is entered (e.g., 7076123456), it should just
format as a phone number, (707) 612-3456.

Anyone have thoughts, suggestions, brilliant solutions?

Thank you!