Truncate phone extension

[Guest]

I run a query for exporting purposes. I do NOT wish to alter my existing
table.

That said, how do I truncate the "ext: ..." from the query results?

As usual, help is greatly appreciated.

 

[Guest]

What about a hint as to what it looks like before truncating and also it
would be helpfull to show the same after it is lopped off.

 

[Guest]

Karl is correct. Without some specifics, all we can recommend is a large
bottle of WhiteOut.

 

[John Spencer]

Field: [Phone Number]: Left([TableName].[Phone Number], INSTR(1,
[TableName].[Phone Number]& " EXT"," Ext",1) -1)

 

[Marshall Barton]

I run a query for exporting purposes. I do NOT wish to alter my existing
table.

That said, how do I truncate the "ext: ..." from the query results?

Use a calculated field in the export query:

Phone: IIf(InStr(phx, "ext:") > 0, Trim(Left(phx, InStr(phx,
"ext:") - 1)), phx)

where phx needs to be replaced by the name of your phone
field.

Note that this expression will not work in VBA where
multiple lines of code would be required.

 

[Guest]

I assumed it was clear...sorry.

Looks like this: (770) 592-8951 ext: 77044
Should look like this: (770) 592-8951

 

[Guest]

I see you have received some solutions. The simplest solution, assuming all
phone numbers are formatted as you show, would be:
Left(PhoneNumber, 14)

 

[Guest]

Thanks! That does the trick.

Thanks to you both.

--
Thanks for your help,
Chris

Field: [Phone Number]: Left([TableName].[Phone Number], INSTR(1,
[TableName].[Phone Number]& " EXT"," Ext",1) -1)