Extarct Text with in string

[NEWER USER]

I have a table with imported text data. I am trying to extract a portion of
text characters within the field. I have had NO success with the Left,
Right, or Mid functions. I would appreciate any help or suggestions.

BH-0265001184 $140.24
05/21/08 11/16/08
BD-DR10GC0 $1.57
05/21/08 11/16/08
BD-HR10AC $1.26
05/21/08 11/16/08
BH-FR7DPP22U $6.72
05/21/08 11/16/08
BJ-13253 $151.55
05/21/08 11/16/08

The data I want (part number) begins on the fourth character. Some part
numbers are 5,6,7,8,9, and could go up to 15 characters. Every other row has
date values and I would like to eliminate these all together as they are not
part numbers. Start on the 4th character and get all up to the first blank
space. Thanks in advance.

 

[KARL DEWEY]

Try this ---
SELECT Mid([YourTable], 4, InStr([YourTable], " ")-1) AS [Part Number]
FROM [YourTable]
WHERE InStr([YourTable], "/") = 0;

 

[NEWER USER]

You nailed it on the first attempt. Thanks so much for all your help.

Try this ---
SELECT Mid([YourTable], 4, InStr([YourTable], " ")-1) AS [Part Number]
FROM [YourTable]
WHERE InStr([YourTable], "/") = 0;

--
KARL DEWEY
Build a little - Test a little

I have a table with imported text data. I am trying to extract a portion of
text characters within the field. I have had NO success with the Left,
Right, or Mid functions. I would appreciate any help or suggestions.

BH-0265001184 $140.24
05/21/08 11/16/08
BD-DR10GC0 $1.57
05/21/08 11/16/08
BD-HR10AC $1.26
05/21/08 11/16/08
BH-FR7DPP22U $6.72
05/21/08 11/16/08
BJ-13253 $151.55
05/21/08 11/16/08

The data I want (part number) begins on the fourth character. Some part
numbers are 5,6,7,8,9, and could go up to 15 characters. Every other row has
date values and I would like to eliminate these all together as they are not
part numbers. Start on the 4th character and get all up to the first blank
space. Thanks in advance.