Excel Hacks Ebook [Electronic resources]

Hack 27 Convert Text Numbers to Real Numbers

The contents of a cell might look like numbers, especially in imported data, but it still might be impossible to use these numbers in calculations. Here are a few ways in which you easily can convert these "text" numbers to true numbers.

Remember that numbers in Excel are right-aligned by default, and text is left-aligned by default. One easy way to identify those problematic text numbers in a column of what you think is composed entirely of true numbers is to select the column, select Format Cells Alignment, ensure that the horizontal alignment is set to Excel's default of General, and click OK. Widen the column to a reasonable width, and all true numbers will be aligned to the right while any problematic text numbers will be aligned to the left. Dates will also be aligned to the right, as a date's true underlying value is nothing more than a number.

Now that you know you have numbers that are being seen as text, here is a quick and easy way to convert them all to true numbers, making Excel consider them usable for calculations. Copy any blank cell and then select your list of numbers. Select Edit Paste Special... and then select Values under the Paste options. Select Add under the Operation options and click OK.

This will change to true numbers any numbers that are being seen as text. This happens because a blank cell has a value of 0, and when you add any number to a number that Excel is treating as text, you will force the text number to become a true number.

You can apply this logic to some of Excel's standard functionsin particular, Excel's TEXT functions. Usually, when you use any of Excel's TEXT functions and the result returned is a number, Excel will still return that number as a text value rather than as a numeric value.

Assume you have a range of cells starting from $A$1. Each cell contains a dollar amount, followed by a space, then a person's name. Using the following formula, which combines the two TEXT functions LEFT and FIND, you can extract this dollar value:

=LEFT(A1,FIND(" ",A1)-1)

If cell A1 contains the data $22.70 Fred, the formula's result will be $22.70. However, this result will be returned as text rather than as a true numeric value; therefore, by default it will be left-aligned within the cell.

You can modify the formula so that the result is no longer a text value, but rather, a true numeric value, by adding 0 to the value:

=LEFT(A1,FIND(" ",A1)-1)+0

This will force the dollar value returned to become a true number; therefore, it will be right-aligned by default. All you need to do now is format the cell accordingly.

Another problem that can arise regarding text and numbers occurs when you mix text and numbers in the same cell, with no real way of extracting the numeric portion only. In this case, you can use a custom function to extract the numeric portion from a text string.

To create this custom function, press Alt/Option-F11, select Insert Module, and enter the following code:

Function ExtractNumber(rCell As Range)
Dim lCount As Long, l As Long
Dim sText As String
Dim lNum As String
sText = rCell
For lCount = Len(sText) To 1 Step -1
If IsNumeric(Mid(sText, lCount, 1)) Then
l = l + 1
lNum = Mid(sText, lCount, 1) & lNum
End If
If l = 1 Then lNum = CInt(Mid(lNum, 1, 1))
Next lCount
ExtractNumber = CLng(lNum)
End Function

Press Alt/-Q and save. The function will appear under User Defined in the Paste function (Shift-F3). Use the function as shown in Figure 2-10.

In Figure 2-10, column A contains a mixture of text and numbers, column B contains the result of using the ExtractNumber function, and column C shows how the formula looks in column B.