Multiple Apps

Parse individual words in a text string

Ease of Use

Intermediate

Version tested with

2000, 2003 

Submitted by:

MWE

Description:

ParseText parses individual words in a text string and returns # of words found and an array of individual words. 

Discussion:

Many applications require the separation of individual words in a text string. The VBA function ?split? will separate words given the delimiter (normally ?blank?) but requires additional processing to determine actual number of words, content of each word, etc. ParseText does that extra processing and returns the number of words found and a string array of individual words. To demonstrate the use of ParseText, a simple test macro is also included. The test macro reads a selected cell, calls ParseText and then displays the results. 

Code:

instructions for use 

			
Option Explicit 
 
Sub Test_ParseText() 
    Dim I As Integer, NW As Integer 
    Dim Cel As Range 
    Dim strBuffer As String, strVal As String, Words(32) As String 
     '
     '           fetch selection text
     '
    For Each Cel In Selection 
        strVal = Cel.Text 
         '
         '               parse fetched text
         '
        Call ParseText(strVal, " ", NW, Words) 
         '
         '               build output data
         '
        strBuffer = "" 
        For I = 1 To NW 
            strBuffer = strBuffer & I & "   " & Words(I) & vbCrLf 
        Next I 
         '
         '               display results
         '
        MsgBox "original text = " & strVal & vbCrLf & _ 
        "# individual words parsed = " & NW & vbCrLf & _ 
        " individual parsed words:" & vbCrLf + strBuffer, vbInformation 
         
    Next Cel 
     
End Sub 
 
Sub ParseText(strBuffer As String, Delim As String, NW As Integer, Words) 
     '
     '       Function    parses individual words from a text string (strBuffer) and returns
     '                   # words found (NW) and a string array of individual words (Words)
     '
    Dim strItems() As String 
    Dim Item As Variant 
     '
     '           parse with Delim
     '
    strItems = Split(strBuffer, Delim) 
     '
     '           determine NW and load Words array for return
     '
    NW = 0 
    For Each Item In strItems 
        NW = NW + 1 
        Words(NW) = Item 
    Next 
     
End Sub

How to use:

  1. Copy the above code.
  2. Open any workbook.
  3. Press Alt + F11 to open the Visual Basic Editor (VBE).
  4. In the left side window, select the target spreadsheet [it will likely be called VBAProject(name.xls) where name is the name of the spreadsheet]
  5. Select an existing code module for the target worksheet; or from the Insert Menu, choose Insert | Module.
  6. Paste the code into the right-hand code window.
  7. Close the VBE, save the file if desired.
  8. See ?Test The Code? below
 

Test the code:

  1. Open the example
  2. Select the cell with ?Now is the ?? or create other text in another cell and select that cell
  3. Go to Tools | Macro | Macros (or Alt-F8) and double-click on Test_ParseText
 

Sample File:

ParseText.zip 8.82KB 

Approved by mdmackillop


This entry has been viewed 99 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express