Quickly convert text values to numbers with Excel's Paste Special

If you regularly copy numerical data from Access tables to Excel spreadsheets, you may know that doing calculations on the copied text is challenging, if possible at all. Here's how to convert those values from their native text versions to more workable number fields in Excel.

After copying data from an Access table, you notice that you cannot perform any calculations on what you thought were number fields. Instead of numbers, the imported values behave as text. By using Excel's Paste Special feature, you can quickly convert those values to numbers. Follow these steps:

1. Click a blank cell formatted as a number cell and enter 1 in the cell.

2. Go to Edit | Copy.

3. Select the cells that contain the text values that you want to convert.

4. Go to Edit | Paste Special.

5. In the Operation section, click Multiply.

6. Click OK.

When importing data from some accounting programs, you may find that negative numbers import as text with the negative sign (-) to the right of the number. For example, suppose after importing, you find the text value 100- in cell H4. To convert that value to a negative number, enter this formula in a blank cell: =LEFT(H4,LEN(H4)-1)*-1.

Miss a tip?

Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.