How can I extract a portion of text from a text string

[Garry]

How can I extract a portion of text from a text string using a query

Examples :

15 Rose Cottage Kent
15 Great Andrew Road London

I require :
Rose Cottage
Great Andrew Street

cheers

 

[Garry]

I should have mentioned that I can extract the numbers and the last word

 

[John Spencer]

IF the structure is always the same as you have described
You can strip off the first word (the numbers) using.

Mid(YourField,Instr(1,YourField," ")+1)

Then you can strip off the last word using

Left(x,InstrRev(x, " ")-1)

Where x is the first expression. So the final expression might look like

Left(Mid(YourField,Instr(1,YourField," ")+1),
InstrRev(Mid(YourField,Instr(1,YourField," ")+1), " ")-1)

Or you could write a custom function in VBA that would strip off the
first and last word.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Garry]

Thank's John, that will do nicely

IF the structure is always the same as you have described
You can strip off the first word (the numbers) using.

Mid(YourField,Instr(1,YourField," ")+1)

Then you can strip off the last word using

Left(x,InstrRev(x, " ")-1)

Where x is the first expression. So the final expression might look like

Left(Mid(YourField,Instr(1,YourField," ")+1),
InstrRev(Mid(YourField,Instr(1,YourField," ")+1), " ")-1)

Or you could write a custom function in VBA that would strip off the first
and last word.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================