You are currently offline, waiting for your internet to reconnect

Sample expressions to extract portion of a text string in Access

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 was previously published under Q286238
Moderate: Requires basic macro, coding, and interoperability skills.

For a Microsoft Access 2000 version of this article, see 209045.
For a Microsoft Access 97 version of this article, see 115915.
SUMMARY
This article lists sample expressions that you can use to extract a portionof 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. You can adapt these expressions so that you can use them with other common formats. For example, you can use the expression that is used to extract "Doe" from "Doe, John" to extract "Seattle" from "Seattle, WA."
MORE INFORMATION
The following expressions are sample expressions that you can use toextract 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.

First Name

Original Entry in [Names]: "John Doe"
Returned by Expression: John
Expression: Expr: Left([Names],InStr(1,[Names]," ")-1)

First Name

Orignial Entry in [Names]: "Doe, John"
Returned by Expression: John
Expression: Expr: Right(Trim([Names]),Len(Trim([Names]))-InStr(1, _
[Names]," "))

First Name

Original Entry in [Names]: "John P. Doe"
Returned by Expression: John
Expression: Expr: Left([Names],InStr(1,[Names]," ")-1)

Last Name

Original Entry in [Names]: = "John Doe"
Returned by Expression: Doe
Expression:Expr: Right(Trim([Names]),Len(Trim([Names]))-InStr(1, _
[Names]," "))

Last Name

Original Entry in [Names]: "Doe, John"
Returned by Expression: Doe
Expression: Expr: Left([Names],InStr(1,[Names],",")-1)

Last Name

Original Entry in [Names]: "John P. Doe"
Returned by Expression: Doe
Expression: Expr: Right(Trim([Names]),Len(Trim([Names]))-InStr(InStr _
(1, [Names]," ")+1,[Names]," "))

Last Name

Original Entry in [Names]: "John Doe" or "John P. Doe"
Returned by Expression: Doe
Expression: Expr: IIf(InStr(InStr([names]," ")+1,[names]," ") _
<>0, Right([names],Len([names])-InStr(InStr([names]," ")+1, _
[names]," ")),Right([names],Len([names])-InStr([names]," ")))

Middle Initial

Original Entry in [Names]: "John P. Doe"
Returned by Expression: P.
Expression: Expr: Trim(Mid([Names],InStr(1,[Names]," ")+1,InStr(InStr _
(1, [Names], " ")+1,[Names]," ")-InStr(1,[Names]," ")))

Middle Initial

Original Entry in [Names]: "Doe, John P."
Returned by Expression: P.
Expression: Expr: Right(Trim([Names]),Len(Trim([Names]))-InStr _
(InStr(1, [Names]," ")+1,[Names]," "))

Middle Initial

Original Entry in [Names]: "John Doe" or "John P. Doe"
Returned by Expression: P. or blank
Expression: Expr: Trim(Mid([names], InStr(1, [names], " ") + 1, IIf(InStr(InStr(1, [names], " ") + 1, [names], " ") = 0, 0, InStr(InStr(1, [names], " ") + 1, [names], " ") - InStr(1, [names], " "))))

Zip Code

Original Entry in [ZipCode]: "98052-6399"
Returned by Expression: 98052
Expression: Expr: Left([ZipCode],5)

Area Code

Original Entry in [Phone]: (425) 635-7050
Returned by Expression: 425
Expression: Expr: Mid([Phone], 2, 3)

Phone Number

Original Entry in [Phone]: (425) 635-7050
Returned by Expression: 635-7050
Expression: Expr: Right(Trim([Phone]),8)
kbnoOfficeAlertID parse parsing text strings fullname city state zip code part partial piece section fragment segment take use ACC2007 ACC2003 ACC2002 reviewdocid
Properties

Article ID: 286238 - Last Review: 03/28/2007 18:25:38 - Revision: 6.1

Microsoft Office Access 2007, Microsoft Office Access 2003, Microsoft Access 2002 Standard Edition

  • kbhowto KB286238
Feedback