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.

Figure 2-10. Extracting the numeric portion from a text string

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.