Specialized custom number format

[BrianW]

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!

 

[Shane Devenshire]

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

 

[Ron Rosenfeld]

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

 

[BrianW]

A brilliant solution - thank you Ron!

Brian

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

 

[Ron Rosenfeld]

A brilliant solution - thank you Ron!

Brian

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