Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Split Function on String with no spaces
Try
Split(rng.Address(True, False), "$") -- HTH Bob Phillips (remove nothere from email address if mailing direct) "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Split Function on String with no spaces
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" 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Split Function on String with no spaces
ExcelMonkey,
You have to use Split ? With ThisWorkbook.ActiveSheet.Range("DF456") Debug.Print .Column & " : " & .Row End With NickHK "ExcelMonkey" wrote in message ... 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" 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Split Function on String with no spaces
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" 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Split Function on String with no spaces
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" wrote in message ... 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" 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Split Function on String with no spaces
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Want to split 922 037 022 into 3cols and recombine without spaces | Excel Worksheet Functions | |||
Find String in another string - only between spaces | Excel Worksheet Functions | |||
Removing Spaces from string | Excel Worksheet Functions | |||
counting spaces in a string | Excel Programming | |||
Count Spaces In A String | Excel Programming |