Numbers are often stored as text when you bring data into Excel from an external program such as a main frame system. You are unable to manipulate and analyze the numeric data when it is stored as text. The good news is you can perform a simple technique to convert the data into numeric values. Here is how:

• Select an empty cell in your worksheet and type a 1.
• Copy the cell containing the 1
• Select the range of data that is stored as text
• Select Edit| Paste Special
• Select Multiply from the Operation section of the dialog box
• Click on OK

All of the data will now be numeric and you are able to analyze and perform mathematical calculations on it.

Negative values can give you problems when the negative sign is shown to the right of the number. Numeric notation in Excel always shows the minus sign to the left. Although a little more complex, you can deal with this in a similar manner. You use a nested formula using the Left and Len functions. The LEFT function returns the first character or characters in a text string, based on the number of characters you specify. The syntax is =LEFT(text,num_chars) where the text argument would reference the cell containing the text string and num_chars is the number of characters you want to return. You use the LEN function to determine the number of characters to return. The LEN function returns the length or number of characters in a text string. It takes on argument (text) which could be a reference to a cell containing the text. When you combine these two functions, you can truncate the right handed minus sign and then multiple the text times -1 value to arrive at the correct negative value. The syntax is:

=LEFT(A2,LEN(A2)-1*-1)

Unfortunately, this technique is not quite as simple as the above conversion from text to numbers. To accomplish it you must use a blank column or row depending on the structure of your data. Establish your formula in a blank cell at the top of an available column. This column does not have to be adjacent to the column you want to convert. Reference the first cell you need to convert for the text argument in both the Left and Len functions. Copy the formula down the column until you reach the end of the data.

Your next step is to replace your actual data with your calculated numeric data. Select the data in the column you just created and copy it. Click in the first cell of the column containing the data needing to be converted. Right click and select Paste Special. Select values in the Paste Special dialog box. Delete the column with the calculated values. You will notice that their values changed because the values in your original column (which is referenced in your formula) changed.

Test the technique out on a small amount of data in a blank worksheet before you use it on a live data analysis.

Are you trying to build on your Excel Skills? Your Bella Desktop Publishing Host is teaching Excel Formulas and Functions – A Proficiency Series at Universal Class.



Be sure to also visit our sister site PC Advice at Bella. You will find a myriad of links and articles about Internet Issues, Personal Computing and Desktop Management.

Please contact your host with any questions. Please also feel free to make any suggestions or contribute a “Unique Technique” Suggestions should be accompanied by your name and title so that I may give the contributor credit.