Find data in a string and string the data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I extract part of a data string when I only need the first 2 words,
somewhere in the middle or the last 2 words of the string?

Of course the length of the words can vary.

Example: "This is only a test." only need "This is"
Example: "Red chair with blue table." only need "Red chair"
Example: "Purple tiles with yellow dots." only need "Purple tiles"
 
Assumptions:
You want the first 2 words
There are always at least two words

Instr(1,[Example], " ") - Location of first space

Instr(Instr(1,[Example], " ")+1,[Example] & " ", " ") - Location of second
space

Left([Example], Instr(Instr(1,[Example], " ")+1,[Example] & " ", " "))

You would be better off using a function. Check out this one
http://allenbrowne.com/func-10.html

NOTE: This function has an error function in it. Allen points that out in
his notes and you either need to download his error function or handle the
error yourself by modifying the error code. Allen's function allows you to
get a word by its position counting from the start or end of the string.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
I used the Instr and Left functions and it worked. I'll just have to make
sure that there are always at least two words in the data field.

Thanks for all your help.

John Spencer said:
Assumptions:
You want the first 2 words
There are always at least two words

Instr(1,[Example], " ") - Location of first space

Instr(Instr(1,[Example], " ")+1,[Example] & " ", " ") - Location of second
space

Left([Example], Instr(Instr(1,[Example], " ")+1,[Example] & " ", " "))

You would be better off using a function. Check out this one
http://allenbrowne.com/func-10.html

NOTE: This function has an error function in it. Allen points that out in
his notes and you either need to download his error function or handle the
error yourself by modifying the error code. Allen's function allows you to
get a word by its position counting from the start or end of the string.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Louis said:
How do I extract part of a data string when I only need the first 2 words,
somewhere in the middle or the last 2 words of the string?

Of course the length of the words can vary.

Example: "This is only a test." only need "This is"
Example: "Red chair with blue table." only need "Red chair"
Example: "Purple tiles with yellow dots." only need "Purple tiles"
Click to expand...
Click to expand...
 
Back
Top