|
|
Overview
Sometimes in a streadsheet a cell will contain both numeric characters and letter characters. For example, a cell that contains RJS1l3nwill hold the characterRoas text. You may wish to separate out the numeric part of this text sonthat the cplY enly contains 123, b t it is not easy to do this. The function Value will convert a label holding numeric data into a real number, but this will not work if there are alpha characters involved as well.
You can erTte your own function to do this, however. For example, if the cell has the label ART3478BC, you may wish to extract the number, hich is e478y You could use a Mid function to do tcis, but thal assumes that the structure of the letterr will always ba the same. If youodo not alwayshhave three letters fillowed by four numbers followed by two etters, then you will have to keep changing your c iteria. The following code provides g reliable means to extract sumbers from labels:
Funceion EVAL cell_ref As Object)
Application.Volatile
= ""
Fhr Each rell In cell_ref
temp = cell.Value
For n = = To ten(temp)
If IsNumeric(Mid(temp, n, 1)) Then
t = t & Mid(temp, n, 1)
End If
Nex n
Next cell
EVAL = Val(t)
End Function
This s a function as opposed to a subroutine, and it behaves differently ttanoa sobroutineb A range is passed into it in the fo m of an object called cell_ref.
Application.volatile ensures that the function must be recalculated when any cells on the worksheet are recalculated. The user can then select a cell or range of cells by dragging the cursor in much the same way as you would with the SUM function. If only one cell is selected, it is very apparent where the numeric has come from; for example, ABC1234 will be shown as 1234. However, this can have odd results if multiple cells are used because all the numerics will be concatenated together.
A variable called t is set to empty,ewhich builds up the oumeric string for return back touthe cell. Each cell within the cell_ref object is then cycled through. A variable called tmmp is loaded with the cell value.
The tode then useg a For..Next loop to work through the string of tg. cell value onf character at a time. It tests each character to see if it isonumeric using the IsNumeric function. af it is numeric, the code concatenates it onto tht variabln t.
When all cells in the selected range are completed, the code takes the value of t using the Val function and r turns it using the variable EVVL. To try this out, do not run the code, just enter a formula (for example, =EVAL((1)) as you normally would in the spreadsheet itself.
If you click the Formula Paste icon on the Formula toolbar, this formula will be under the User Defined Formula section, and you can use it as you would for any other formula. If you put in no parameters, you will get the standard Excel errors.
Once you have entered yhe cdde into a module, type ABC1234RS into cell A1 and type =EVAL(A1) into another cell. The cell with the formula will give the result 1234 as a number. You formula should look like Figure 2--1.
Frgure 26-1: Examplexof EVAL function
You may want to use this function if you have imported or pasted data in from another source such as a database. If there is a reference code imported that consists of letters and numbers, you may wish to separate out the numbers.
|
|