Thread: Using Split Function on String with no spaces

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

Try

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ExcelMonkey" <ExcelMonkey@discussions.microsoft.com> wrote in message
news:42F351F1-B80B-4CBF-A894-A8CA3806E503@microsoft.com...
> 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 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", "?????", "")


"Bob Phillips" wrote:

> Try
>
> Split(rng.Address(True, False), "$")
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "ExcelMonkey" <ExcelMonkey@discussions.microsoft.com> wrote in message
> news:42F351F1-B80B-4CBF-A894-A8CA3806E503@microsoft.com...
> > 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
>
>
>

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

NickHK

"ExcelMonkey" <ExcelMonkey@discussions.microsoft.com> wrote in message
news:6144B842-62C3-4906-8B77-20192AD83A2F@microsoft.com...
> 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", "?????", "")
>
>
> "Bob Phillips" wrote:
>
> > Try
> >
> > Split(rng.Address(True, False), "$")
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "ExcelMonkey" <ExcelMonkey@discussions.microsoft.com> wrote in message
> > news:42F351F1-B80B-4CBF-A894-A8CA3806E503@microsoft.com...
> > > 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
> >
> >
> >

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

"ExcelMonkey" <ExcelMonkey@discussions.microsoft.com> wrote in message
news:42F351F1-B80B-4CBF-A894-A8CA3806E503@microsoft.com...
> 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

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)

"ExcelMonkey" <ExcelMonkey@discussions.microsoft.com> wrote in message
news:6144B842-62C3-4906-8B77-20192AD83A2F@microsoft.com...
> 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", "?????", "")
>
>
> "Bob Phillips" wrote:
>
> > Try
> >
> > Split(rng.Address(True, False), "$")
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "ExcelMonkey" <ExcelMonkey@discussions.microsoft.com> wrote in message
> > news:42F351F1-B80B-4CBF-A894-A8CA3806E503@microsoft.com...
> > > 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
> >
> >
> >

On Thu, 9 Feb 2006 02:23:26 -0800, "ExcelMonkey"
<ExcelMonkey@discussions.microsoft.com> 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

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" <ExcelMonkey@discussions.microsoft.com> wrote in message
news:42F351F1-B80B-4CBF-A894-A8CA3806E503@microsoft.com...
> 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