Specialized custom number format
 

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!

Specialized custom number format
 

"BrianW" wrote in message
...
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!




use IF statements.......

Specialized custom number format
 

Hi,

What version of Excel are you using? 2003 or 2007.

In 2003 you can do this with a formula in a separate column or with VBA.
However, I would be supprised that everytime you enter 1 you want 551 - how
do you enter 661?

In 2007 you can do this with conditional formatting -
Home, Conditional Formatting, Manage Rules, New Rule, 6th choice and enter
the formula:
=AND(LEFT(B1)="1",LEN(B1)=5)
then click Format, Number, Custom and enter
(212) 55#-####
on the Type line.

Repeat as necessary.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

"BrianW" wrote:

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!

Specialized custom number format
 

On Wed, 26 Nov 2008 11:41:01 -0800, BrianW
wrote:

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!


Suggestion: Use an event triggered macro:

Right click on the sheet tab and select View Code.
Paste the code below into the window that opens. Modify the range to the one
you wish to have affected.

========================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range

'set r to the appropriate range
Set r = Range("A:A")

Application.EnableEvents = False
If Not Intersect(Target, r) Is Nothing Then
For Each c In Intersect(Target, r)
c.NumberFormat = "(000) 000-0000"
Select Case c.Value
Case 10000 To 19999
c.Value = CDbl(21255 & c.Value)
Case 20000 To 29999
c.Value = CDbl(21266 & c.Value)
Case 30000 To 39999
c.Value = CDbl(21277 & c.Value)
Case 1000000 To 9999999
c.Value = CDbl(212 & c.Value)
Case 1000000000 To 9999999999#
c.Value = c.Value
Case Else
'presumeably an invalid entry
'not sure what you want to happen
'so I've left it unchanged
c.NumberFormat = "General"
c.Value = c.Text
End Select
Next c
End If
Application.EnableEvents = True
End Sub
======================================
--ron

Specialized custom number format
 

A brilliant solution - thank you Ron!

Brian

"Ron Rosenfeld" wrote:

On Wed, 26 Nov 2008 11:41:01 -0800, BrianW
wrote:

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!


Suggestion: Use an event triggered macro:

Right click on the sheet tab and select View Code.
Paste the code below into the window that opens. Modify the range to the one
you wish to have affected.

========================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range

'set r to the appropriate range
Set r = Range("A:A")

Application.EnableEvents = False
If Not Intersect(Target, r) Is Nothing Then
For Each c In Intersect(Target, r)
c.NumberFormat = "(000) 000-0000"
Select Case c.Value
Case 10000 To 19999
c.Value = CDbl(21255 & c.Value)
Case 20000 To 29999
c.Value = CDbl(21266 & c.Value)
Case 30000 To 39999
c.Value = CDbl(21277 & c.Value)
Case 1000000 To 9999999
c.Value = CDbl(212 & c.Value)
Case 1000000000 To 9999999999#
c.Value = c.Value
Case Else
'presumeably an invalid entry
'not sure what you want to happen
'so I've left it unchanged
c.NumberFormat = "General"
c.Value = c.Text
End Select
Next c
End If
Application.EnableEvents = True
End Sub
======================================
--ron

Specialized custom number format
 

On Wed, 26 Nov 2008 13:31:01 -0800, BrianW
wrote:

A brilliant solution - thank you Ron!

Brian

Glad to help. Thanks for the feedback.
--ron