adding characters

[rangebob]

I have 600 records that I've imported and the telephone numbers have no - in them. How do I add the dash into the phone number?

[andygaunt]

Are your numbers just 5551234567

If so then create a calculation field (FormatNumber, result of text)

"(" & Left(Phone Number,3) & ") " & Middle(Phone Number, 4, 3) & "-" & Right(Phone Number, 4)

This will return (555) 123-4567

HTH

[Lee Smith]

I would use a calculation field to format it.

Take a look at the file "Phone Format" available at

http://www.datatude.net/

go to Tips and Downloads.

HTH

[jasonwood]

Just to clarify, the above responses won't really do what you have asked, because they involve a separate field for display. However, this is a better way to go.

Set the new calculation field so that it does not allow entry into field, then place it overtop the existing phone field. You will see the formatted field, but when you click, the cursor goes into the field below. Then make it a rule that staff never enter phone numbers with any formatting, and you ensure that all phone numbers will be formatted the same way on the screen.

[andygaunt]

Or, use the calculation and do a calculated replace on the data instead. Then the phone number field (which must be a text field) will have the valid format)

Be careful when using the replace function. Try on a copy of the database first or on a small (1 or two records) set of data to make sure the result is what you expect.

[rangebob]

Thanks for all the replys. You are all too helpful.