Visual Basic > Applications-VBA

Split a given string into different words

Split a given string into different words

You have a function to split strings in words on your list, there is an
improvement (new from scratch coded), the older does not work proprly,
and the new one have the delimiter as a variable, not "," forced.

There is the code.


Function SplitString%(TheString$, Delim$, DynArray$())
    dim p%, t%
    dim sTempString$, tmp$
    t% = 0
    ' Remove trailing blanks
    sTempString$ = Trim(TheString$)
    ' If the last character is the delimiter, remove it
    If Right(sTempString$, 1) = Delim$ Then sTempString$ = Left(sTempString$, Len(sTempString$) - 1)
    ' Add a delimiter to end
    sTempString$ = sTempString$ & Delim$
    Do
        p% = InStr(sTempString$, Delim$)
        If p% = 0 Then exit Do
        ReDim Preserve DynArray$(t%)
        tmp$ = Left(sTempString$, p% - 1)
        sTempString$ = Right$(sTempString$, Len(sTempString$) - p%)
        DynArray$(t%) = Trim(tmp$)
        t% = t% + 1
    Loop
    ' Returns the last availabe index
    SplitString% = UBound(DynArray$)
End Function

** Calling procedure example:

' text$ is the string to be splitted
' n= number of words found - 1
' delimiter is "," 

Dim Words$()
Text$ = Trim(Text$)
n = SplitString(Text$, ",", Words$())



Return