how do I add phone number format as a permanent custom format?

Every Excel spreadsheet I receive includes column(s) somewhere of phone numbers. Rather than have to recreate in EACH file a new custom format I dream of being able to PERMANENTLY add two telephone number formats so that I can EASILY apply the following formats: ###-###-#### and ###-###-#### ###. Is there any way on EARTH that I can PERMANENTLY add these formats to MS's available formats?

Remember: I DO NOT CREATE new Excel spreadsheets, I only receive them, so templates would be useless.

frustrated...

Hi

Open a new workbook

Goto Format>Cells>Custom

Enter the 2 phone number formats
Ok

Select cell A1
Goto Edit>Clear>All

Goto File>Save As
File name: Book
Save as Type: Template (*.xlt)
Save the file to your XLSTART folder

Close Excel
Restart Excel and you'll have your custom formats available

Biff

"frustratedagain"
<[email protected]> wrote in
message news:[email protected]...
>
> Every Excel spreadsheet I receive includes column(s) somewhere of phone
> numbers. Rather than have to recreate in EACH file a new custom format
> I dream of being able to PERMANENTLY add two telephone
> number formats so that I can EASILY apply the following formats:
> ###-###-#### and ###-###-#### ###. Is there any way on EARTH that I
> can PERMANENTLY add these formats to MS's available formats?
>
>
> Remember: I DO NOT CREATE new Excel spreadsheets, I only receive them,
> so templates would be useless.
>
> frustrated...
>
>
> --
> frustratedagain
> ------------------------------------------------------------------------
> frustratedagain's Profile:
> http://www.excelforum.com/member.php...o&userid=30997
> View this thread: http://www.excelforum.com/showthread...hreadid=506639
>

fa,

If you know how to add a custom macro button to a toolbar and
can find the Personal.xls file then paste this code into the Personal.xls file
and attach it to the custom Toolbar button.
Then select the cells you want to format and click the new button.
------------------------------
Sub JustWhatIWanted()
'Jim Cone - San Francisco, USA - January 2006
'Adds a telephone number format to all cells in the selection'
Dim rngSelection As Excel.Range
Dim rngCell As Excel.Range
'Make sure the following line does not wrap (is a single line).
Const strFormat As String = "[<=9999999]###-####;[<=9999999999]###-###-####;###-###-###-####"

If TypeName(Selection) <> "Range" Then
MsgBox "Select some cells on the worksheet. ", vbInformation, "Special Number Format"
Exit Sub
End If
Set rngSelection = Excel.Selection
For Each rngCell In rngSelection
rngCell.NumberFormat = strFormat
Next 'rngCell
End Sub

'-------------------------------------------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"frustratedagain" wrote in message...
Every Excel spreadsheet I receive includes column(s) somewhere of phone
numbers. Rather than have to recreate in EACH file a new custom format
I dream of being able to PERMANENTLY add two telephone
number formats so that I can EASILY apply the following formats:
###-###-#### and ###-###-#### ###. Is there any way on EARTH that I
can PERMANENTLY add these formats to MS's available formats?

Remember: I DO NOT CREATE new Excel spreadsheets, I only receive them,
so templates would be useless.
frustrated...
frustratedagain

Jim,

That's a big IF...

you wrote: "If you know how to add a custom macro button to a toolbar and
can find the Personal.xls file then paste this code into the Personal.xls file
and attach it to the custom Toolbar button.
Then select the cells you want to format and click the new button."

Where can I go to learn that?

frustratedagain