Using Split Function on String with no spaces
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
--
Regards,
Tom Ogilvy
"ExcelMonkey" wrote in message
...
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
|