×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel Phone Number Hyphen Insertion
2

Excel Phone Number Hyphen Insertion

Excel Phone Number Hyphen Insertion

(OP)
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

RE: Excel Phone Number Hyphen Insertion

2
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

RE: Excel Phone Number Hyphen Insertion

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

-InspEngr

RE: Excel Phone Number Hyphen Insertion

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

RE: Excel Phone Number Hyphen Insertion

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

RE: Excel Phone Number Hyphen Insertion

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.

RE: Excel Phone Number Hyphen Insertion

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

Thank you.

PUMPDESIGNER

RE: Excel Phone Number Hyphen Insertion

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'

RE: Excel Phone Number Hyphen Insertion

...OR a formula


Mala Singh
'Dare to Imagine'

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close