Unfortunately I don't really understand how to use Excel's Visual Basic
function. I have entered the code you provided into a Module and saved it,
however I am still having trouble.
1) I dont think I understand what an outline number is. Could you give me
an example? I have been entering something like this: =combine(A2).
2) Regardless of what I put into the combine function, it says there is a
syntax error and highlights the first line of code.
I am working with Microsoft Office Excel 2003 (11.6355.6408) SP1 if that
makes any difference.
Also, I actually have up to 8 numbers separated by decimals, however I
expect I can change that by simply changing Const MaxLevels As Long = 4 to
=8 ?
Thank you very much for your help.
To enter the UDF, <alt<F11 opens the VB editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.
Be sure to note the MaxNum constant which is the Maximum Number of outline
sublevels you might have. In your example, you showed four, so that is what I
used. In addition, I have assumed that the maximum number of levels within each
sublevel is 99.
In a column adjacent to your table, enter the formula:
=combine(cell_ref) (where cell_ref is the outline number)
and copy/drag down as far as needed.
Then sort on this new column (ascending).
=================================
Function combine(rg As Range) As Double
Const dot As String = "."
Const NullString As String = ""
Const MaxLevels As Long = 4 'Maximum number of levels
Const MaxSubLevels = 99 'Maximum number of sublevels in each level; must be
10^x-1
Dim i As Long
Dim temp
temp = Split(rg.Text, dot)
For i = 0 To UBound(temp)
combine = combine + temp(i) * 10 ^ (((MaxLevels - 1) - i) _
* Log(MaxSubLevels + 1) / Log(10))
Next i
End Function
==================================
HTH,
--ron