Microsoft KB Archive/286238

= Sample expressions to extract portion of a text string in Access =

Article ID: 286238

Article Last Modified on 3/28/2007

-

APPLIES TO


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

-



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 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 &quot;Doe&quot; from &quot;Doe, John&quot; to extract &quot;Seattle&quot; from &quot;Seattle, WA.&quot;



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]: &quot;John Doe&quot;

Returned by Expression: John

Expression: Expr: Left([Names],InStr(1,[Names],&quot; &quot;)-1)

First Name
Orignial Entry in [Names]: &quot;Doe, John&quot;

Returned by Expression: John

Expression: Expr: Right(Trim([Names]),Len(Trim([Names]))-InStr(1, _

[Names],&quot; &quot;))

First Name
Original Entry in [Names]: &quot;John P. Doe&quot;

Returned by Expression: John

Expression: Expr: Left([Names],InStr(1,[Names],&quot; &quot;)-1)

Last Name
Original Entry in [Names]: = &quot;John Doe&quot;

Returned by Expression: Doe

Expression:Expr: Right(Trim([Names]),Len(Trim([Names]))-InStr(1, _

[Names],&quot; &quot;))

Last Name
Original Entry in [Names]: &quot;Doe, John&quot;

Returned by Expression: Doe

Expression: Expr: Left([Names],InStr(1,[Names],&quot;,&quot;)-1)

Last Name
Original Entry in [Names]: &quot;John P. Doe&quot;

Returned by Expression: Doe

Expression: Expr: Right(Trim([Names]),Len(Trim([Names]))-InStr(InStr _

(1, [Names],&quot; &quot;)+1,[Names],&quot; &quot;))

Last Name
Original Entry in [Names]: &quot;John Doe&quot; or &quot;John P. Doe&quot;

Returned by Expression: Doe

Expression: Expr: IIf(InStr(InStr([names],&quot; &quot;)+1,[names],&quot; &quot;) _

<>0, Right([names],Len([names])-InStr(InStr([names],&quot; &quot;)+1, _

[names],&quot; &quot;)),Right([names],Len([names])-InStr([names],&quot; &quot;)))

Middle Initial
Original Entry in [Names]: &quot;John P. Doe&quot;

Returned by Expression: P.

Expression: Expr: Trim(Mid([Names],InStr(1,[Names],&quot; &quot;)+1,InStr(InStr _

(1, [Names], &quot; &quot;)+1,[Names],&quot; &quot;)-InStr(1,[Names],&quot; &quot;)))

Middle Initial
Original Entry in [Names]: &quot;Doe, John P.&quot;

Returned by Expression: P.

Expression: Expr: Right(Trim([Names]),Len(Trim([Names]))-InStr _

(InStr(1, [Names],&quot; &quot;)+1,[Names],&quot; &quot;))

Middle Initial
Original Entry in [Names]: &quot;John Doe&quot; or &quot;John P. Doe&quot;

Returned by Expression: P. or blank

Expression: Expr: Trim(Mid([names], InStr(1, [names], &quot; &quot;) + 1, IIf(InStr(InStr(1, [names], &quot; &quot;) + 1, [names], &quot; &quot;) = 0, 0, InStr(InStr(1, [names], &quot; &quot;) + 1, [names], &quot; &quot;) - InStr(1, [names], &quot; &quot;))))

Zip Code
Original Entry in [ZipCode]: &quot;98052-6399&quot;

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)

Additional query words: kbnoOfficeAlertID parse parsing text strings fullname city state zip code part partial piece section fragment segment take use ACC2007 ACC2003 ACC2002 reviewdocid

Keywords: kbhowto KB286238

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.