Split Function

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
 
F

Frank Kabel

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
 
A

Andy Wiggins

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"
 
T

Tom Ogilvy

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
 
A

Andy Wiggins

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"
 
Top