Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 30
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 83
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,346
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 5,651
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 30
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 5,651
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Custom number format for driver's license number excel user Excel Discussion (Misc queries) 10 July 16th 08 10:05 PM
Moving custom number format to NUMBER Doug Boufford Setting up and Configuration of Excel 3 July 23rd 07 11:58 PM
how do I add phone number format as a permanent custom format? frustratedagain Excel Discussion (Misc queries) 3 February 4th 06 04:52 AM
Format a cell with a custom number format Armor Excel Worksheet Functions 4 January 30th 06 12:25 AM
Custom number format always defaults last number to 0. scubadave Excel Discussion (Misc queries) 2 June 15th 05 10:20 PM


All times are GMT +1. The time now is 12:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"