seperate numbers from letters in a field

[Guest]

I have a databse that has one field that contains part numbers and
descriptions. I would like to make two fields 1) with part numbers, and 2)
part description.
Is there a code or query that I can create to seperate the numbers from the
letters. Some part numbers have letters in them.
I would like to seperate them in groups. If a word contains numbers it is a
part number, if only letters than part description.

 

[RBear3]

Is the part number a specific length? If so, you can use LEFT, RIGHT, or
MID to pull out the portions you want.

Give us a few examples.

 

[John W. Vinson]

On Thu, 31 May 2007 11:40:01 -0700, Access G-man <Access

I have a databse that has one field that contains part numbers and
descriptions. I would like to make two fields 1) with part numbers, and 2)
part description.
Is there a code or query that I can create to seperate the numbers from the
letters. Some part numbers have letters in them.
I would like to seperate them in groups. If a word contains numbers it is a
part number, if only letters than part description.

There is a function IsNumeric() which will return True if the argument is
numeric, and False if it isn't. You could run a query

SELECT * FROM yourtable WHERE IsNumeric([Partnumber])

to find all records where the field Partnumber can be expressed as a number.
One caution: the string "314E6" is in fact numeric, since it's scientific
notation for 314000000.

John W. Vinson [MVP]

 

[BruceM]

Is there a space between the part number and the description? If there is
just one space, you could use InStr in a query. Make a query based on the
table, with all of the fields. I will call the original field [NumDesc].
In query design view, at the top of a blank column, use something like:
PN: Left([NumDesc],InStr([NumDesc]," ")-1)
At the top of another blank column:
Desc: Right([NumDesc],Len([NumDesc] - InStr([NumDesc]," "))
You could run the query as a make-table query.
This will work if there is a space in the Description, but not if there is a
space in the part number. If the situation is other than a space after the
part number, there may be no single simple solution, but if you post details
it may be possible to come up with something.