FAQ: How to convert values to text in Excel using the TEXT function.

Here's a tip for Excel to allow you to load organizational or survey job codes that are a mix of number codes and letter codes. For example, some surveys have job numbers similar to the following:

• 1000
• 1000R

or the format may look like this

• 10.10
• 10.11
• 10.12

What can happen in Excel is that the trailing zero is removed and the list ends up looking like this:

• 10.1
• 10.11
• 10.12

In the above examples the job codes with an R at the end are considered text data by Excel. The job codes without the R are considered numeric data by Excel. Similarly, the job codes with the decimal point are all considered values by Excel, when in reality you want them to load into NextComp as text.

NextComp will read only the numeric OR text depending on how the data is sorted in the import template. You’ll need to convert all of the job codes to text data in Excel. The image below gives a quick visual summary of the process. Click the image for a larger version.


Here's a step-by-step description for Excel 2003 and prior versions. The same techniques work in Excel 2007, although the menu names may be slightly different.

Step 1: Select all of column B by clicking in column B’s header

Step 2: Select the Insert menu in Excel, then select Columns

Step 3: Select the Format menu item in Excel and then select the Cells menu item

Step 6: Select cell B2

Step 7: Enter the formula shown below into cell B2. Here's a description of the TEXT function on the Microsoft Office website.

Step 8: Copy cell B2 down all the way to the last row of job code data

Step 9: Select data in column B from cell B2 through to the last row of data in the column

Step 10: Select the Edit menu item in Excel and then select the Copy menu item

Step 11: Select cell A2

Step 12: Select the Edit menu item in Excel and then select the Paste Special... menu item

Step 13: Choose “Values” as the Paste type

Step 14: Click the OK button

Step 15: Select all of column B by clicking on the column header for B

Step 16: Select the Edit menu in Excel and then select the Delete menu item

Step 17: Select the File menu item in Excel

Step 18: Select the Save item in the File menu

Excel Tip: Paste Special | Multiply

Here's another trick that I've been using in preparing data files for loading into NextComp.

Many surveys display the survey result amounts in 1,000's, for example, "$54,500" is displayed as "54.5" in the Excel report from the publisher.

NextComp expects the survey data to be in hourly or annual amounts. If we load the data as it is displayed, NextComp will assume that the data represents an hourly amount of $54.50.

You can use the Copy, Paste Special | Multiply function to convert all the data in a column or a group of columns to an annual figure.

Here's how...

The image below shows a column of data before being converted to annual figures.



I entered the number 1000 in the empty cell at the top of the column, as shown below.



I copied the cell with the 1000 in it. I selected all the data in column AK. And I right clicked over the selected cells to bring up the menu shown below.



I chose the Paste Special... command from the menu. In the Paste Special dialog box, I selected the "Multiply" operation.



After clicking the OK button, the data in the selected cells is multiplied by the copied value, in this case 1000.



You can use this same method to divide data as well. Just choose the "Divide" operation in the Paste Special dialog box. This can be useful when loading data that NextComp expects to be in a percent format. Some surveys display 100% as the number 100 rather than 1 with formatting applied.