Extracting part numbers

[PD]

The field contains verbiage such as this;

"Cust. needs P136547896, 635216, 209157."
or
"Could no find assembly number, kit is 456789."

I need to pull each part number out into its own column (if multiples
given), I tried Mid with InStr but the string locations vary. The part
numbers maintain the formats show.

Thanks,

 

[John W. Vinson]

The field contains verbiage such as this;

"Cust. needs P136547896, 635216, 209157."
or
"Could no find assembly number, kit is 456789."

I need to pull each part number out into its own column (if multiples
given), I tried Mid with InStr but the string locations vary. The part
numbers maintain the formats show.

Thanks,

Ow. So you want to find EITHER a string consisting of a single letter followed
by nine digits, or any string consisting of six digits, anywhere within a memo
field of free text? What if they text contains "needs it by 032708 at the
latest", or a six digit address?

You'll probably have to write some nontrivial VBA code or use a Regular
Expressions tool to search for these. You can find which records contain such
strings using a criterion

LIKE "* [A-Z]#########*" OR LIKE "* ######[., ]"

but that doesn't extract the part number as a value.

 

[PD]

Thanks John,

I was able to make this work.
--
PRD

The field contains verbiage such as this;

"Cust. needs P136547896, 635216, 209157."
or
"Could no find assembly number, kit is 456789."

I need to pull each part number out into its own column (if multiples
given), I tried Mid with InStr but the string locations vary. The part
numbers maintain the formats show.

Thanks,

Ow. So you want to find EITHER a string consisting of a single letter followed
by nine digits, or any string consisting of six digits, anywhere within a memo
field of free text? What if they text contains "needs it by 032708 at the
latest", or a six digit address?

You'll probably have to write some nontrivial VBA code or use a Regular
Expressions tool to search for these. You can find which records contain such
strings using a criterion

LIKE "* [A-Z]#########*" OR LIKE "* ######[., ]"

but that doesn't extract the part number as a value.