Using Split Function on String with no spaces

[Guest]

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

 

[Bob Phillips]

Try

Split(rng.Address(True, False), "$")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Bob I have already extracted the address as a string. So I will not be using
the range object with the address property. I think what your code does is
relace a "$" with a "". I don't have a "$" to replace. I simply want to
split the string inbetween the F and the 3.

Split("DF345", "?????", "")

 

[NickHK]

ExcelMonkey,
You have to use Split ?
With ThisWorkbook.ActiveSheet.Range("DF456")
Debug.Print .Column & " : " & .Row
End With

NickHK

 

[kounoike]

What about making a short function like this

Sub test()
Dim str As String
Dim a
str = "DF345"
a = exnum(str)
MsgBox a(1) & " : " & a(0)
End Sub

Function exnum(ByVal s As String) As Variant
Dim arr()
ReDim arr(1)
arr(0) = StrReverse(Val(StrReverse(s)))
arr(1) = Replace(s, arr(0), "")
If arr(1) = "" Then
ReDim Preserve arr(0)
End If
exnum = arr
End Function

keizi

 

[Bob Phillips]

What I am suggesting is to use the source, before you extract the address,
and extract it as column absolute.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Ron Rosenfeld]

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

 

[Tom Ogilvy]

Here is a another approach

Public Function SplitA(s As String, _
rw As String, col As String)
On Error GoTo ErrHandler
s1 = UCase(s)
Select Case True
Case s1 Like "[A-Z][A-Z]*"
col = Left(s, 2)
Case s1 Like "[A-Z]*"
col = Left(s, 1)
End Select
rw = Right(s, Len(s) - Len(col))
SplitA = True
Exit Function
ErrHandler:
SplitA = False
End Function

Usage
Dim saddr as String, srow as String, scol as String
aAddr = "DF456"
If SplitA(addr, srow, scol) then
msgbox "Row: " & srow & ", Col: " & scol
else
msgbox "Bad address"
end if