Section 11.1. Numbers

11.1. Numbers

When is a number not a number? When it's text, of course. Numbers imported from a mainframe, a text file, or lifted from a report often end up as text in Excel. If math functions do not work on a column of imported numbers, some or all of them were imported as text.

Often the VALUE function will fix the problem. The VALUE function attempts to convert the contents of a cell to a number. The value is returned if it is successful, and if not, a #VALUE! error is set.

In the example in Figure 11-1 the data is from a mainframe and the invoice amounts have been imported as text. Cell B2 contains the formula =VALUE(A2). This formula is filled down to B16. Cells A18 and B18 contain =SUM(A2:A16) and =SUM(B2:B16). The formula in A18 does not work because Excel does not know these are numbers. In column B the VALUE function has converted the text to numbers and the SUM function in B18 works properly.

Figure 11-1. Numbers as text

Sometimes the problem is more complicated. In the next example some of the values are negative. Our data source uses CR (Credit) to indicate the number is negative. Excel cannot handle this, so the VALUE function will not help.

A custom function solves the problem. In this case the VALUE function has been replaced with the custom function CleanNumber, which takes two arguments. The first is the text containing the number. The second is the marker that tells when the number is negative. Figure 11-2 shows how it is used.

CleanNumber uses the TextVersion string to build a second string containing only the valid numeric characters. It then converts the second string to a numeric value. In this example the function is entered as =CleanNumber(A11,"CR"). Here is the code for CleanNumber:

 Function CleanNumber(TextVersion As String, NegMarker As String) As Double '************************************************** ' This Function will eliminate all non-numeric ' characters from the string TextVersion and ' will return the value of the numeric part of ' the string. If the string NegMarker is ' present in TextVersion the value will be ' returned as negative. '*************************************************** Dim NegValue, x As Integer Dim OneByte, cleanText As String 'First we check for the NegMarker If InStr(TextVersion, NegMarker) > 0 Then ' If NegMarker is found in TextVersion     NegValue = -1                         ' Set NegValue to -1 Else                                      ' If NegMarker is not found     NegValue = 1                          ' Set NegValue to 1 End If ' Next we loop through TextVersion and check each character to see ' if it is numeric. Only the numbers are kept. For x = 1 To Len(TextVersion)               ' Loop for the length of TextVersion     OneByte = Mid(TextVersion, x, 1)        ' The character in TextVersion we                                             ' are looking at now     If IsNumeric(OneByte) Then              ' If it is numeric         CleanText = CleanText & OneByte     ' put it on the end of CleanText     End If Next x ' CleanText now contains the value but still as a string. ' It can now be converted to a number. It is multiplied by ' NegValue so that negative values will be returned correctly. CleanNumber = Val(CleanText) * NegValue End Function 

Figure 11-2. Numbers as text in non-standard format

You can add this function to your Excel project by selecting Tools Macro Visual Basic Editor. You can also launch the editor by pressing Alt-F11. Just type or paste the code into the editor and the CleanNumber function is ready to use.

The first line in the code starts with the word Function. This means the code can be used on the workbook. Once this code in loaded into a project, the CleanNumber function works just like any other workbook function. Not all macros are functions. If the first word was Sub instead of Function it would be necessary to run the macro each time it is needed.

Sometimes even CleanNumber is not enough, though. Consider the example in Figure 11-3.

Figure 11-3. Complex numeric data as text

Here there is an account code in front of the invoice amount. The account code occupies four characters at the beginning of each number. The CleanNumber function will return the wrong value if used alone. The answer is to combine CleanNumber with the RIGHT workbook function. The formula in B2 is =CleanNumber(RIGHT (A2,LEN(A2)-4),"CR"). We want to skip the first four characters. The RIGHT function returns the right portion of the string starting at any position. In this case RIGHT starts at position LEN(A2)-4. This value is four less than the length of A2, and the function will return all but the first four characters. RIGHT function works inside the CleanNumber function.

If the unwanted characters were at the end of the string, the LEFT function would be used. The MID function would also work and is the best choice if the important information is in the middle of the string.

If you are dealing with this kind of problem, it is worth the effort to learn the LEFT, MID, and RIGHT functions. Using them with VALUE or CleanNumber is a powerful tool for cleaning up dirty data.

Figure 11-4 shows how these functions work. The string in cell A1 is broken up into three parts using LEFT, RIGHT, and MID. In all three, the first parameter is the source string. In the LEFT and RIGHT functions the second parameter specifies the number of characters to be taken. The MID function has three parameters giving the source string, starting position, and number of characters.

Figure 11-4. LEFT, MID, and RIGHT