Parsing a multi-word Text String

[wilgrant]

I need to parse a text string that will have seven words within the string, each separated by one or more spaces. I want to parse each word into it's own string (i.e. String1, String2,,,,, String7).

Does anyone know of a routine that will accomplish this no matter what the length of each individual word in the string?

Thanks

 

[rmikesmith]

You can use the Split function if you are using Office 2000 or later. Here is some example code:

Dim sText As String
Dim vParsed As Variant

   sText = "This   is a test of string parsing"
   sText = Application.WorksheetFunction.Trim(sText)
   vParsed = Split(sText, " ", -1, vbTextCompare)

vParsed is a variant array that will contain each word in sText as an individual element. You can use vParsed directly just as you would any array or can assign individual string variables (your String1, String2, etc.). For example, the following uses vParsed directly:

For i = LBound(vParsed) To UBound(vParsed)
  Debug.Pring vParsed(i)
Next i

or assginment to individual variables:

String1 = vParsed(0)
String2 = vParsed(1)
...
String7 = vParsed(6)

Note the use of the Trim function. This removes extra spaces between the words in the original string, if they exist. This is necessary for the Split function to work properly (otherwise, some elements may be empty strings). There is also a VBA version of Trim, but it only removes spaces at the beginning and end of a string.

Hope this helps.
Mike

 

[rmikesmith]

Just realized I let my Excel prejudice get the better of me. The line of code
sText = Application.WorksheetFunction.Trim(sText) is specific to Excel; it won't work in, say, Word. One way to handle this (if you're not using Excel) is to strip the extra spaces from the input string using a custom function. Here is one I use:

Function TrimExtraInternalSpaces(ByVal sInp As String, Optional ByVal TrimEnds As Boolean = False) As String
Dim InWhiteSpace As Boolean
Dim i As Integer
Dim Length As Integer
Dim OneChar As String * 1
Dim sTmp As String

   If TrimEnds Then sInp = Trim(sInp)
   Length = Len(sInp)
   If Length = 0 Then
     TrimExtraInternalSpaces = ""
     Exit Function
   End If
   sTmp = ""
   For i = 1 To Length
     OneChar = Mid$(sInp, i, 1)
     If Asc(OneChar) = 32 Then
       If InWhiteSpace Then
         'skip it
       Else
         InWhiteSpace = True
         sTmp = sTmp & OneChar
       End If
     Else
       sTmp = sTmp & OneChar
       InWhiteSpace = False
     End If
   Next i
   TrimExtraInternalSpaces = sTmp
   
End Function

Using this, my previous code would now look like:

Dim sText As String
Dim vParsed As Variant

   sText = "This   is a test of string parsing"
   sText = TrimExtraInternalSpaces(sText, True)
   vParsed = Split(sText, " ", -1, vbTextCompare)

Alternatively, you could leave the extra spaces then check for empty strings in vParsed and ignore.

Regards,
Mike

 

[macropod]

Hi Mike,

A simpler way:

Sub TrimString()
Dim MyString As String
MyString = InputBox("Input String")
MyString = Trim(MyString)
Do While InStr(1, MyString, "  ") > 0
    MyString = Replace(MyString, "  ", " ")
Loop
MsgBox "Trimmed String: " & MyString
End Sub

Cheers

[MS MVP - Word]

 

[rmikesmith]

macropod -- Excellent! More compact and faster, to boot.


Regards,
Mike

 

[comaboy]

hmmm, but the OP wants the individual words in their own strings. May I make suggestion based on macropod's function?
(it's basically the same thing, but goes on to Split the returned string into a string array.)

Sub TrimString()
    Dim MyString As String
    Dim strOutput() As String
    Dim x As Long
    MyString = InputBox("Input String")
    MyString = Trim(MyString)
    Do While InStr(1, MyString, "  ") > 0
        MyString = Replace(MyString, "  ", " ")
    Loop
    strOutput = Split(MyString, " ")
    For x = 0 To UBound(strOutput)
        Debug.Print strOutput(x)
    Next x
End Sub