Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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....... |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom number format for driver's license number | Excel Discussion (Misc queries) | |||
Moving custom number format to NUMBER | Setting up and Configuration of Excel | |||
how do I add phone number format as a permanent custom format? | Excel Discussion (Misc queries) | |||
Format a cell with a custom number format | Excel Worksheet Functions | |||
Custom number format always defaults last number to 0. | Excel Discussion (Misc queries) |