Working with CarePrecise CSV files in Excel®

About Leading Zeroes in Excel

CarePrecise provides our CarePrecise Select custom data, state databases, and fax number lists in CSV format (Comma Separated Values), which Excel can open directly. But, if you do that, you may lose leading zeroes where they appear in the Zip Code® ("Zip") column. If this is a problem with your use of Excel, the fix is to change the file into an Excel (.xlsx) file. It happens in a few steps.

1. Close the file, save a copy of it, and in Windows Explorer, change its file extension from ".csv" to ".txt". You may see a message to the effect that the file may become unstable; ignore this.
2. Next, open Excel (the program itself, not by clicking on the data file), and then "Open" the file in Excel. It will pop up an Import wizard.
3. In the first screen ("Step 1") of the Import wizard, select "Delimited" and checkmark "My data has headers." Click "Next".
4. In "Step 2" of the Import wizard, checkmark "Comma" for the Delimiters (and deselect any delimiters that were already selected).
5. In "Step 3" of the Import wizard, select the Zip Code column, and change its "Column data format" to "Text". Click Finish.
6. When the file opens, click "File" | "Save As" and for file type, select "Excel Workbook (*.xlsx)" to save the file in Excel.

After following these steps, your Excel file (".xlsx" file) will show all of the 5-digit the zip codes with leading zeroes properly.

How to Remove Duplicates in Excel

After opening your file in Excel, use Excel's de-duplication tool.

1. Save a copy of the file. Select the range of cells that has duplicate values you want to remove.
2. Click "Data" | "Remove Duplicates," and then under "Columns," check or uncheck the columns where you want to remove the duplicates.
3. In the box next to values with, pick the formatting you want to apply to the duplicate values, and then click OK.
4. In the "Remove Duplicates" popup, choose the column that contains the duplicate value you want to deduplicate, for instance, the "Fax" column. Click "OK"

In that example, the list now contains only unique fax numbers.

How to change the fax or phone number format

Preparing for your upload to a sending service may take a couple of extra steps, depending on your service's requirements.

You may see phone and fax numbers with hyphens (-), for instance: 915-555-6254. If you are planning to send bulk faxes, you may want to remove the hyphen. That's easy in Excel, as is appending a "1" at the front of the number if your sending service requires it.

IMPORTANT: Before you change the file with the instructions below, save a copy of the file in case anything untoward obtains!

Formatting with added spaces or hyphens (or anything else you like)

In Excel, in a new column (in this example, the number is in the A2 cell), type this formula:
=LEFT(A2,3) & " " & MID(A2,4,3) & " " & RIGHT(A2,4)

You can copy that formula all the way down the column to the last row by selecting the cell with the formula in it, then moving your mouse to the bottom right corner of the cell and double-clicking the fill handle (the little + sign) that appears.

• If you want hyphens instead, put a "-" between the quotes where we've put a space in the formula, like
  =LEFT(A2,3) & "-" & MID(A2,4,3) & "-" & RIGHT(A2,4)
  
  
• If you want the old-school phone number format, like (908) 555-7384, use this formula instead:
  ="(" & LEFT(A4,3) & ") " & MID(A4,4,3) & "-" & RIGHT(A4,4)
  
  

Removing hyphens from a fax number

Some fax sending services want just the numerals, without the hyphens, in the fax numbers. Here's how...

1. In Excel, save the file as an xlsx file, and add a new column.
2. Create a formula in the top cell of the new column (under the column name): =SUBSTITUTE(J2,"-","") where J2 is the top fax number column cell.
3. Drag-copy the new cell down to the last row.
4. Refer to your Excel documentation, or search Google, for additional information and formatting techniques.

To append a "1" at the front of the phone or fax number:

Some fax sending services want a "1" at the beginning of our U.S. fax numbers. Here's how...

1. In Excel, save the file as an xlsx file, and add a new column.
2. Create a formula in the top row of the new column (under the column name): ="1" & J2 where J2 is the top fax number column cell.
3. Copy the new cell down to the last row.
4. To both append a "1" and remove hyphens, the formula is: ="1" & SUBSTITUTE(J2,"-","")
5. Refer to your Excel documentation, or search Google, for additional information and formatting tricks.
   

To move the PracFax or MailFax cell into a single column

On CarePrecise Select custom lists, you will see two fax columns – the PracFax (practice location fax number) and the MailFax (the fax number at the record's mailing address). If they are different, you may prefer one or the other, depending on your application. That's pretty easy to accommodate. We'll name this column "PreferredFax" and put a formula in it that will look first in one of the fax cells and use that value if it has one, and if it doesn't, it will look for a value the other cell. Weirdly, this uses Excel's IsNumber function, but we'll be looking for a hyphen (-), not actually a number, because our fax number cells look like "000-000-0000".

1. In Excel, save the file as an xlsx file, and add a new column.
2. Create a formula in the top row of the column (under the column name):
   =IF(ISNUMBER(SEARCH("-",U2)),U2,IF(ISNUMBER(SEARCH("-",W2)),W2,""))
   where U2 is the PracFax cell and W2 is the MailFax cell. If you prefer the MailFax instead of the PracFax, just reverse U2 and W2 in the formula.
3. Refer to your Excel documentation, or search Google, for additional information and formatting tricks.

Formatting a number for automated dialing

How about we create a new column that does all of this at once – removes hyphens, appends a "1" at the front, and uses the preferred fax number? Easy as pie.

1. Save a copy of the file. In Excel, add a new column.
2. Create a formula in the top row of the column (under the column name):
   =SUBSTITUTE(IF(ISNUMBER(SEARCH("-",U2)),"1"&U2,IF(ISNUMBER(SEARCH("-",W2)),"1"&W2,"")),"-","")
   where U2 is the PracFax cell and W2 is the MailFax cell. If you prefer the MailFax instead of the PracFax, just reverse U2 and W2 in the formula.
3. Refer to your Excel documentation, or search Google, for additional information and formatting tricks.

See a menu of all of our fax number databases...