Split function Array out of range

[silentwolf]

Hi guys,

I would like to split text in vba

Function ViertesWort(varText As Variant)
    Dim varWoerter As Variant
    
    varWoerter = Split(varText, " ")
'    ViertesWort = varWoerter(3)
    
'    If varWoerter(3) = "" Then
'        Debug.Print "Es ist leer"
'    End If
    
    'Also wrong
    If IsNull(varWoerter(3)) Then
        Debug.Print "Leer"
    End If
    
End Function

So the above code splits string into an array.
But as I don't know the size of the array if the "The sentence is" like this then I will not have an array with varWoerter(3)

and it throughs an error.

I tried to catch it with the above code but in both ways I did not be able to work it out.

Mybe someone can tell me?

I know with ubound I find the last string in the array but if I don't know how long the array is in the first place then it does not work.

The reason behind this is I like to get some information out of the text but don't need to have a longer text split all the way.

Maximum to four
so varWoerter(3)


Thanks for your help!

 

[FormR]

How about..

Function ViertesWort(varText As String, n As Long) As String
    ViertesWort = Split(varText & Space(n), " ")(n - 1)
End Function

Used like..

Book1
	A	B	C	D	E
1	one two three four five six	one	two	three	four
2	one two three four	one	two	three	four
3	one two three	one	two	three
4	one two	one	two
5	one	one
Sheet1

Cell Formulas
Range	Formula
B1:B5	B1	=ViertesWort($A1,1)
C1:C5	C1	=ViertesWort($A1,2)
D1:D5	D1	=ViertesWort($A1,3)
E1:E5	E1	=ViertesWort($A1,4)

 

[silentwolf]

Hi FormR,

yes that works!

Cheers )