Article ID: 286238 - Last Review: March 28, 2007 - Revision: 6.1 Sample expressions to extract portion of a text string in AccessThis 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 (http://support.microsoft.com/kb/209045/EN-US/ ) .
For a Microsoft Access 97 version of this article, see 115915
(http://support.microsoft.com/kb/115915/
)
.
SUMMARY
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. 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 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. 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) | Article Translations
|
| United States | Change | | | All Microsoft Sites |
Back to the top
|
