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!

 

[Micron]

You want to get what information out of the string - the last array element? Don't understand what problem you're referring to regarding UBound. This would get the last array element regardless of how long the string is, but it would include punctuation:
Debug.Print varWoerter(UBound(varWoerter))

Input: ViertesWort "The sentence is just long enough."
Output: enough.

 

[Colo]

Hi silentwolf,

The index of an array by the Split function starts from 0. So let's say, the Ubound of the string "The sentence is" returns 2. If you'd like to know what's in variables, debugging with the Watch Window helps you.

 

[Micron]

The index of an array by the Split function starts from 0.

I figure that fact is known because of

Maximum to four
so varWoerter(3)

Perhaps that is not the case though.

 

[silentwolf]

Hi thanks for your replies!

Sorry this is not what I am after. I understand the watch window.

The reason behind this is following

I got a Column in Excel housing articles and descriptions.
But for me relevant parts are within the first three or four words.

for example "*P* Druckschlauchtülle d40"
Now I like to split those into new columns so I created this function to do so.

the above would be varWoerter(2)
If I have another text in the same column "Druckerplatte 1254" there would be no varWoerter(2) as it contains only (1) elements.

So how can I if that is the case return from the function an empty string rather then an error?

Hope that is better explained?

Cheers

 

[FormR]

So how can I if that is the case return from the function an empty string rather then an error?

Hi, to always get the third element or a null string if there isn't one - you could try something like this.

Function ViertesWort(varText As String) As String
    ViertesWort = Split(varText & Space(3), " ")(2)
End Function

 

[Alex Blakenburg]

See if this gives you any ideas:

Sub TestSplit()
    Dim varText As String
    Dim varWoerter As Variant
       
    varText = "*P* Druckschlauchtülle d40"
    varWoerter = Split(varText)
    ReDim Preserve varWoerter(0 To 3)
    Debug.Print UBound(varWoerter) & vbTab & varWoerter(0) & " / " & varWoerter(1) & " / " & varWoerter(2) & " / " & varWoerter(3)

    varText = "Druckerplatte 1254"""
    varWoerter = Split(varText)
    ReDim Preserve varWoerter(0 To 3)
    Debug.Print UBound(varWoerter) & vbTab & varWoerter(0) & " / " & varWoerter(1) & " / " & varWoerter(2) & " / " & varWoerter(3)

End Sub

Note: You could store all your split results in an array and do the Redim Preserve in a loop
Dim arr (1 to 10000) as variant
i = i + 1
arr(i) = varWoerter

Additional Ref, my post here: Excel VBA formula for split rows and columns doesn't work

 

[silentwolf]

Hi thanks for your replies,

I will look at it and see if I can manage to work it out!

Cheers

 

[Colo]

Hope I could understand what you want.
I took "relevant parts are within the first three or four words" which means that words more than 4 are not necessary. So I cut off the array maximum 3 upper bounds.

Sub test()
    Range("A1").Resize(, 3).Value = ViertesWort("*P* Druckschlauchtulle d40")
    Range("A2").Resize(, 3).Value = ViertesWort("Druckerplatte 1254")
End Sub

Function ViertesWort(varText As Variant)
    Dim varWoerter As Variant
    varWoerter = Split(varText, " ")

    'Cut off storage space more than 3
    ReDim Preserve varWoerter(3)

    ViertesWort = varWoerter
End Function

 

[silentwolf]

Hi well I guess we are getting there,

The reason behind this is that I like to extract data from one column and put it into another column.

There are parts thousand of them in one column and to be able to filter I like to split them into different columns so I know that certain parts belong to certain "Categories" so to say.

Some of the information I need firstWord, secondWord, ThirdWord, ForthWord.

But the "Column can contain 10 "Words" but I dont need those I just need to first four Words as the other information is not relevant for splitting!

In my Excel sheet then I want that formular in the column something like that " =firstWord(A1) ; = secondWord(A1), =thirdWord(A1), =forthWord(A1)

Sometimes the information I need is the firstWord, sometimes the second and so on.. but sometimes there is only one or two or three or four or more words in that column

That is why I need a function not a sub I need to return values and put it back into my column so I know that part is for what Category.

For future filtering!

Cheers