Excel Phone Number Hyphen Insertion 2

[PUMPDESIGNER]

I have scrounged several thousand phone numbers off a database.
Able only to put the 10 digit phone numbers into one cell.
Is there a method to insert the hyphens into the phone numbers so they are easier to read?
Other than hard work that is.

Thank you for all the past help you guys have been.

PUMPDESIGNER

 

[BillPSU]

Pumpdesigner

The following formula will do the trick.

="("&MID(A1,1,3)&")"&MID(A1,4,3)&"-"&MID(A1,7,4)

A1 is the cell with the 10 digit phone number.

Bill

 

[InspEngr]

Highlight the cells then use the following menu commands:
Format-Cell-Number(tab)-Custom
Type ###-###-#### into the "Type" box.

-InspEngr

 

[Mala]

1. Select the cells containing the phone numbers

2. From the menu select:
Format>Cells>Number>Special>Phone Number

This should format the ten-digit phone numbers in the standard phone no. format i.e.
'(###) ###-####'


'Dare to Imagine'
Mala Singh

 

[SomeYahoo]

Nifty formula/function BillPSU! That'll be good for part numbers (hyphened in most spots, but not in exports from the main dBase).

 

[BillPSU]

GensetGuy

You can also create a calculated field in the dBase/SQL query and export the information in the format you want. I've done both depending on what was needed.

In an Access report you can actually add the parentheses and the dashes in the report leaving the raw data alone. If you are exporting data from some large application have your IS person write the query and save it so that the information comes out with the same format every export.

I used to download data from a mainframe on a daily basis and used the data to update multiple database tables. The data was then queried to supply information to supplies for MRP/requirement data. I would compress the data and encrypt it and the suppliers could pickup the data from a website which held the files for the suppliers. I furnished a program to the suppliers which would unecrpyt and uncompress the data and put it in a table and could view the data. It was a poor man's EDI.

 

[PUMPDESIGNER]

BillPSU,
Yup, works great.
I tried many ways thinking Excel would surely have some formatting, but nothing I tried worked.

Thank you.

PUMPDESIGNER

 

[Mala]

Excel does have the standard telephone number formatting you need built into it...you do not have to specify a custom number format.




Mala Singh
'Dare to Imagine'

 

[Mala]

...OR a formula



Mala Singh
'Dare to Imagine'