Extracting Different Parts of Our SKU

[meangene]

Our SKUs consist of a 2-3 digit pattern#, a dash ("-") delimeter, a 2-3 digit
suffix and 1-2 alphabetical characters (A-Z) representing a color (if one
exists). Examples:

No Color
123-456
12-345
123-45

With Color
123-456AB
12-34AB
456-789C

I need to separate the SKUs into their component parts as separate columns,
less the delimiter. Examples:

Pattern# Suffix Color
123 456
12 345
456 789 C
12 34 AB

I can extact the Pattern# using
Left([ItemNumber],(InStr(1,[ItemNumber],"-")-1)), but because the remainder
of the item number is not consistant in the number of suffix and color
characters, can't seem to break them out cleanly. Any help would be greatly
appreciated!

 

[Golfinray]

Use the mid command. mid([your field name],starting position, how many
positions) Like this mid([your field],4,4) would start at the 4th number and
take the next 4 numbers.

 

[meangene]

Golfinray - Thanks but don't think Mid will work consistantly in this
situation. For SKU 123-456 Mid ([ItemNumber],5,3) will return the suffix 456
OK, but same command on SKU 12-345 will give me 45 , which is incorrect. I'm
thinking that if there is a function that recognizes alphabetical characters
(A-Z) like I used InStr to pivot off the "-" I can grab characters after the
delimiter ("-") and before any alphabetical characters (the color).

Use the mid command. mid([your field name],starting position, how many
positions) Like this mid([your field],4,4) would start at the 4th number and
take the next 4 numbers.

Our SKUs consist of a 2-3 digit pattern#, a dash ("-") delimeter, a 2-3 digit
suffix and 1-2 alphabetical characters (A-Z) representing a color (if one
exists). Examples:

No Color
123-456
12-345
123-45

With Color
123-456AB
12-34AB
456-789C

I need to separate the SKUs into their component parts as separate columns,
less the delimiter. Examples:

Pattern# Suffix Color
123 456
12 345
456 789 C
12 34 AB

I can extact the Pattern# using
Left([ItemNumber],(InStr(1,[ItemNumber],"-")-1)), but because the remainder
of the item number is not consistant in the number of suffix and color
characters, can't seem to break them out cleanly. Any help would be greatly
appreciated!

 

[KARL DEWEY]

Try this --
SELECT meangene.ItemNumber, Left([ItemNumber],(InStr(1,[ItemNumber],"-")-1))
AS [Pattern#], Val(Mid([ItemNumber],InStr(1,[ItemNumber],"-")+1)) AS Suffix,
Right([ItemNumber],Len([ItemNumber])-(Len(Left([ItemNumber],(InStr(1,[ItemNumber],"-")+Len(Val(Mid([ItemNumber],InStr(1,[ItemNumber],"-")+1)))))))) AS Color
FROM meangene;

--
KARL DEWEY
Build a little - Test a little

Golfinray - Thanks but don't think Mid will work consistantly in this
situation. For SKU 123-456 Mid ([ItemNumber],5,3) will return the suffix 456
OK, but same command on SKU 12-345 will give me 45 , which is incorrect. I'm
thinking that if there is a function that recognizes alphabetical characters
(A-Z) like I used InStr to pivot off the "-" I can grab characters after the
delimiter ("-") and before any alphabetical characters (the color).

Use the mid command. mid([your field name],starting position, how many
positions) Like this mid([your field],4,4) would start at the 4th number and
take the next 4 numbers.

Our SKUs consist of a 2-3 digit pattern#, a dash ("-") delimeter, a 2-3 digit
suffix and 1-2 alphabetical characters (A-Z) representing a color (if one
exists). Examples:

No Color
123-456
12-345
123-45

With Color
123-456AB
12-34AB
456-789C

I need to separate the SKUs into their component parts as separate columns,
less the delimiter. Examples:

Pattern# Suffix Color
123 456
12 345
456 789 C
12 34 AB

I can extact the Pattern# using
Left([ItemNumber],(InStr(1,[ItemNumber],"-")-1)), but because the remainder
of the item number is not consistant in the number of suffix and color
characters, can't seem to break them out cleanly. Any help would be greatly
appreciated!

 

[meangene]

I will - thanks!

Try this --
SELECT meangene.ItemNumber, Left([ItemNumber],(InStr(1,[ItemNumber],"-")-1))
AS [Pattern#], Val(Mid([ItemNumber],InStr(1,[ItemNumber],"-")+1)) AS Suffix,
Right([ItemNumber],Len([ItemNumber])-(Len(Left([ItemNumber],(InStr(1,[ItemNumber],"-")+Len(Val(Mid([ItemNumber],InStr(1,[ItemNumber],"-")+1)))))))) AS Color
FROM meangene;

--
KARL DEWEY
Build a little - Test a little

Golfinray - Thanks but don't think Mid will work consistantly in this
situation. For SKU 123-456 Mid ([ItemNumber],5,3) will return the suffix 456
OK, but same command on SKU 12-345 will give me 45 , which is incorrect. I'm
thinking that if there is a function that recognizes alphabetical characters
(A-Z) like I used InStr to pivot off the "-" I can grab characters after the
delimiter ("-") and before any alphabetical characters (the color).

Use the mid command. mid([your field name],starting position, how many
positions) Like this mid([your field],4,4) would start at the 4th number and
take the next 4 numbers.

:

Our SKUs consist of a 2-3 digit pattern#, a dash ("-") delimeter, a 2-3 digit
suffix and 1-2 alphabetical characters (A-Z) representing a color (if one
exists). Examples:

No Color
123-456
12-345
123-45

With Color
123-456AB
12-34AB
456-789C

I need to separate the SKUs into their component parts as separate columns,
less the delimiter. Examples:

Pattern# Suffix Color
123 456
12 345
456 789 C
12 34 AB

I can extact the Pattern# using
Left([ItemNumber],(InStr(1,[ItemNumber],"-")-1)), but because the remainder
of the item number is not consistant in the number of suffix and color
characters, can't seem to break them out cleanly. Any help would be greatly
appreciated!