ACCESS: How to Parse Comma-Separated Text into Multiple Fields

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

This article has been archived. It is offered "as is" and will no longer be updated.
Summary
This article provides two methods that you can use to parse comma-separated text in a Text field and to display the text in multiple Text fields.

You can use the first method for a Text field that contains two words separated by a comma, for example, a field that contains a last namefollowed by a first name (Smith, John).

You can use the second method for a Text field that contains more than two words separated by commas, for example, a field that contains a city, a region, and a country (Toronto, Ontario, Canada).
More information
In order to parse comma-separated Text fields, and to display the resulting text strings in multiple Text fields, use one of the methods listed below:

Method 1

This method uses an expression in a query that includes three functions: the Instr() function to search for the comma in the Text field, and the Left$() and Right$() functions to extract the two parts of the Text field.

To parse a Text field that contains two words separated by a comma, follow these steps:
  1. Start Microsoft Access, and then open any existing database.

    Create a table with the following structure:
    Table: Parse2Words
    ------------------
    Field Name: Empl
    Data Type: Text
  2. View the Parse2Words table in Datasheet view, and then type the following three records in the Empl field:
    Smith, John
    Callahan, Laura
    Fuller, Andrew
  3. Create the following query based on the Parse2Words table:
       Query: QueryTest   ------------------------------------------------------------------   Field: FirstName: Right$([Empl],Len([Empl]) - InStr(1,[Empl],",") -1)      Show: True   Field: LastName: Left$([Empl],InStr(1,[Empl],",") -1)      Show: True						
    NOTE: You can modify the QueryTest query to account for spaces between the two parts in the Empl field. For example, if the text in the Empl field is Smith,John without spaces, remove the "-1" from the FirstName field expression.
  4. Run the query. Note that the QueryTest query separates the text in the Empl field into the two fields below:
       FirstName   LastName   --------------------   John        Smith   Laura       Callahan   Andrew      Fuller					

Method 2

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. This method uses two user-defined functions: a function named CountCSWords() to count the number of comma-separated words in the Text field, and a function named GetCSWord() to return the nth word in the Text field.

To parse a Text field that contains more than two words separated by commas, follow these steps:
  1. Start Microsoft Access, and then open any database.
  2. Create a table with the following structure:
    Table: ParseWords
    --------------------
    Field Name: Location
    Data Type: Text
  3. View the ParseWords table in Datasheet view, and then type the following three records in the Location field:
    Toronto, Ontario, Canada
    Boston, Massachusetts, USA
    Vancouver, British Columbia, Canada
  4. Create a module, and then type Option Explicit in the Declarations section if it is not already there.
  5. Type the following procedures:
    Function CountCSWords(ByVal S) As Integer' Counts the words in a string that are separated by commas.Dim WC As Integer, Pos As Integer   If VarType(S) <> 8 Or Len(S) = 0 Then     CountCSWords = 0     Exit Function   End If   WC = 1   Pos = InStr(S, ",")   Do While Pos > 0     WC = WC + 1     Pos = InStr(Pos + 1, S, ",")   Loop   CountCSWords = WCEnd FunctionFunction GetCSWord(ByVal S, Indx As Integer)' Returns the nth word in a specific field.Dim WC As Integer, Count As Integer, SPos As Integer, EPos As Integer   WC = CountCSWords(S)   If Indx < 1 Or Indx > WC Then     GetCSWord = Null     Exit Function   End If   Count = 1   SPos = 1   For Count = 2 To Indx     SPos = InStr(SPos, S, ",") + 1   Next Count   EPos = InStr(SPos, S, ",") - 1   If EPos <= 0 Then EPos = Len(S)   GetCSWord = Trim(Mid(S, SPos, EPos - SPos + 1))End Function					
  6. Compile the module, save it as basParse, and then close it.
  7. Create the following query based on the ParseWords table:
       Query: QueryTest2   ---------------------------------------   Field: City: GetCSWord([Location],1)      Show: True   Field: Region: GetCSWord([Location],2)      Show: True   Field: Country: GetCSWord([Location],3)      Show: True					
  8. Run the query. Note that the QueryTest2 query separates the text in the Location field into the three fields below:
       City        Region             Country   --------------------------------------   Toronto     Ontario            Canada   Boston      Massachusetts      USA   Vancouver   British Columbia   Canada					
References
For additional information about programming resources for Visual Basic for Applications, click the article number below to view the article in the Microsoft Knowledge Base:
226118 OFF2000: Programming Resources for Visual Basic for Applications

For additional information about parsing text strings, click the article number below to view the article in the Microsoft Knowledge Base:
209045 ACC2000: Sample Expressions to Extract Portion of Text String
split how to
Properties

Article ID: 210588 - Last Review: 01/11/2015 02:06:10 - Revision: 4.0

  • Microsoft Access 2000 Standard Edition
  • Microsoft Access 2002 Standard Edition
  • Microsoft Office Access 2003
  • Microsoft Office Access 2007
  • Microsoft Access 2010
  • kbnosurvey kbarchive kbhowto kbusage KB210588
Feedback