ACC: Sample Expressions to Extract Portion of Text String

ID: Q115915

The information in this article applies to:

Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article lists sample expressions that you can use to extract a portion of a text string. These expressions are commonly used in the Update To line of an update query to place a portion of a larger field into a new field. These expressions can be adapted for use with other common formats. For example, the expression used to extract "Doe" from "Doe, John" can be used to extract "Seattle" from "Seattle, WA."

MORE INFORMATION

The following expressions are sample expressions that you can use to extract a portion of a text string.

NOTE: In the following sample expressions, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating the expression.


          Original Entry                  Returned by Expression
 --------------------------------------------------------------------

 [Names] = "John Doe"                      John
 Expr: Left([Names],InStr(1,[Names]," ")-1)

 [Names] = "John Doe"                      Doe
 Expr: Right(Trim([Names]),Len(Trim([Names]))-InStr(1,[Names]," "))

 [Names] = "Doe, John"                     Doe
 Expr: Left([Names],InStr(1,[Names],",")-1)

 [Names] = "Doe, John"                     John
 Expr: Right(Trim([Names]),Len(Trim([Names]))-InStr(1,[Names]," "))

 [Names] = "John P. Doe"                   Doe
 Expr: Right(Trim([Names]),Len(Trim([Names]))-InStr(InStr(1,[Names],_
      " ")+1,[Names]," "))

 [Names] = "John P. Doe"                   John
 Expr: Left([Names],InStr(1,[Names]," ")-1)

 [Names] = "John P. Doe"                   P.
 Expr: Mid([Names],InStr(1,[Names]," ")+1,InStr(InStr(1,[Names],_
      " ")+1,[Names]," ")-InStr(1,[Names]," "))

 [Names] = "Doe, John P."                  P.
 Expr: Right(Trim([Names]),Len(Trim([Names]))-InStr(InStr(1,[Names],_
      " ")+1,[Names]," "))

 [Names] = "John Doe"  or  [Names] = "John P. Doe"     Doe
 Expr: IIf(InStr(InStr([names]," ")+1,[names]," ")<>0, _
      Right([names],Len([names])-InStr(InStr([names]," ")+1, _
      [names]," ")),Right([names],Len([names])-InStr([names]," ")))

 [ZipCode] = "98052-6399"                  98052
 Expr: Left([ZipCode],5)

 [Phone] = (206) 635-7050                  206
 Expr: Mid([Phone],2,3)

 [Phone] = (206) 635-7050                  635-7050
 Expr: Right(Trim([Phone]),8)

For more sample expressions, see the Neatcode.mdb sample databases. For details on how to obtain these databases, please see the following articles in the Microsoft Knowledge Base:

Q148287 ACC2: NEATCOD2.MDB Available on MSL

Q148402 ACC95: NEATCODE.MDB Available on MSL

Additional query words: parse name first last parsing text strings fullname city state zip code string functions


Keywords          : kbusage ExrOthr 
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: April 3, 1999