Split Function

  • Thread starter Thread starter TAM
  • Start date Start date
T

TAM

I am trying to use the split function to return several instances of
substrings within a string. Can you please tell me how to return those
values e.g. to a variable

Thanks

TAM
 
Hi
one way:

Sub foo()
Dim Text_Split As Variant
Text_Split= Split(ActiveCell.Value)
MsgBox Text_Split(0)
MsgBox Text_Split(1)
End Sub
 
Copy the routine (below) into a module and run it. The results will be
printed to the immediate window using Debug.Print

The result of Split is returned as an array. If you know how many elements
are in the array (UBound - LBound) then you can address whichever one you
want. In the example, to capture the third element to a variable called
CapVal, you would use:
CapVal = y(2)

'' *********************************************************
'' Purpose : Using "Split"
'' Written : 21-Feb-2004 by Andy Wiggins - Byg Software Ltd
''
Sub UsingSplit()
Dim x
Dim y
Dim z
Dim c
x = "c:\temp\aaa\bill.xls"
y = Split(x, "\")
z = UBound(y) - LBound(y)

For c = LBound(y) To UBound(y)
Debug.Print y(c)
Next

End Sub


--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"
 
z = UBound(y) - LBound(y)

should be

z = UBound(y) - LBound(y) + 1

1 to 10 = 10 - 1 + 1 = 10

0 to 9 = 9 - 0 + 1 = 10
 
Actually, it's not necessary and shouldn't even be there :-(

I forgot to delete it before postng the code. Doh!

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"
 
Back
Top