On Thu, 9 Feb 2006 02:23:26 -0800, "ExcelMonkey"
wrote:

I have a cell address - assume its relative - "DF456". I want to split this
up by colunm address and row address "DF" and "456". I was hoping I could
use the Split funciton But I am not sure what delimiter to use if I am going
to use the Split function - if its even applicable.

Or am I forced to loop through the string find the starting/ending points of
Letters/Numbers and use the replace function to redefine (i.e.
replace("DF456","DF", "") = "456" and replace("DF456","456", "") = "DF"

Thanks

EM


You could use "regular expressions". These are available either by setting a
reference to Microsoft VBScript Regular Expressions 5.5 and setting things up
within the function, or by downloading and installing Longre's free
morefunc.xll add-in from http://xcell05.free.fr/

and using the functions built in to that.

The expression to use will depend on whether or not you wish to return the '$'
if the reference is an absolute one.

But the following is an example of the use with morefunc.xll installed:

======================
Sub foo()
Const str As String = "$IV$65535"
Dim s1 As String, s2 As String

s1 = Run([regex.mid], str, "\$?[A-Z]+")
s2 = Run([regex.mid], str, "\$?\d+")

Debug.Print s1
Debug.Print s2

End Sub
======================

If you never want the '$' returned, then just change the lines:

===================
s1 = Run([regex.mid], str, "[A-Z]+")
s2 = Run([regex.mid], str, "\d+")
===================


--ron