Multiple Apps

Parse individual words in a text string

Ease of Use


Version tested with

2000, 2003 

Submitted by:



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


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. 


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: 8.82KB 

Approved by mdmackillop

This entry has been viewed 96 times.

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