I am trying to export a list of customers. I am using the find feature (F6) and generating a list of all customers. Then I press the Excel button. This is really to be a call list.

The problem is that all fields have the data that I want but start with a "'"

For instance our business phone shows as '2095775067 instead of 2095775067.

This stops me from being able to format the cell. I can not find a way to make it appear as (209)577-5067.

I'm sure this question has been posted before, but I'm not sure how to search for it.

Hi Nick,

Telephone numbers are stored in the data as 7 or 10 digit numbers. Using the SmartFinder can only deal with the raw data and not apply a text mask to 'format' the phone number.

Perhaps an Excel macro can be run on the exported Excel file in order to change the phone number to text and insert the brackets and dash. Or perhaps even better, use Adagio GridView to perform the export (instead of the SmartFinder) as you can create a calculated column to format the phone number prior to exporting to Excel.

Oh yeah, Excel can do it for me. Maybe I wasn't clear.

The ' hash mark that adagio puts in front of the number stops the format cell from working.

So if the number is '2095775067 I can not format it, but if the number is 2095775067 (without the hash mark in front), Excel converts it to (209)577-5067 just fine.

In the end, you're right, I should use gridview.

OK. The leading apostrophe is there because the phone number is defined as text, so that it can be formatted with non-numeric characters and also support alpha characters. You cannot change the data field format with SmartFinder, it simply exports as is.

GridView is really the way to go for simplicity.

Either:

Highlight the column in Excel and select the General format.

or

Assuming the telephone numbers are in column F, insert a new column G with a formula like =Concatenate("(",left(F4,3),") ",Mid(F4,4,3),"-",right(F4,4))

Then either hide Column G or open column H, copy column G and "Paste Special"/Values into column H. Then delete columns F and G.

To rid yourself of the hash mark open the Excel spreadsheet, highlight the column, press CTL+H (search & replace), put a ' for find what, and nothing for the replace with. This will remove the '.

Sorry, Excel will not find the '

I got a GridView set up, but It does not format the telephone number correctly either!

As mentioned in the previous post, you have to create a calculated column to add the formatting:

eg for a 10-digit number:

"(" & LEFT({Phone number},3) & ")" & MID({Phone number},4,3) & "-" & RIGHT({Phone number},4)

If you have a mix of 7 and 10-digit phone numbers, you have to create a condition that checks for the phone number field length and if 7 then format one way and if 10 format another way.