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)