Un-Concatenate text before and after a hyphen symbol ..... -

  • Thread starter Thread starter Gary Shelton
  • Start date Start date
G

Gary Shelton

I have some sku data that is all grouped together with sku and sku
description. I want to seperate it out and account for the Discontinued data
as well which is designated with a (D), (D1), (D2), or (DD) symbol in the
text.

NOTE: the data has two space bar tabs at the begining that I also need to
get ride of...

Sample DATA below:

SKU
0080 - OREIDA GOLDEN PATTY 12X20.25
0084 - OREIDA HASH COUNTRY 12X27
0192 - OREIDA TOASTER HASH 12X7
0783D - O-I MASH POTATOES SUP 12X22
0862N - OREIDA IQF HASH CLUB 6X6
ACH03903 - (D2) AMERICAS CHOICE RIPPLE BEER FF 12X28
ACH03905 - (D1) AMERICAS CHOICE BUFFALO FRIES 12X28
ACH03906 - (DD) AMERICAS CHOICE GARLIC FF 12X28
ACH03908 - (D) AMERICAS CHOICE GARLIC FF 12X28
10310688 - MCC 4/3.75 BACON EGG CHS POTATO PATTY
10310688 - MCC 4/3.75 BACON EGG CHS POTATO PATTY
10310688 - MCC 4/3.75 BACON EGG CHS POTATO PATTY
BCH04545 - (D2) BEST CHOICE TATER PUFFS 6X4LB
BCI00255 - BREW CITY 5/16 SC 6X5

What I would like to have is three columns with the following data
unconcatenated:

SKU Discontinued Sku Description
0080 OREIDA GOLDEN PATTY
12X20.25
0084 OREIDA HASH COUNTRY
12X27
0192 OREIDA TOASTER HASH 12X7
0783D O-I MASH POTATOES SUP
12X22
0862N OREIDA IQF HASH CLUB 6X6
ACH03903 (D2) AMERICAS CHOICE RIPPLE BEER
FF 12X28
ACH03905 (D1) AMERICAS CHOICE BUFFALO FRIES
12X28
ACH03906 (DD) AMERICAS CHOICE GARLIC FF 12X28
ACH03908 (D) AMERICAS CHOICE GARLIC FF
12X28
10310688 MCC 4/3.75 BACON EGG CHS
POTATO PATTY
10310688 MCC 4/3.75 BACON EGG CHS
POTATO PATTY
10310688 MCC 4/3.75 BACON EGG CHS
POTATO PATTY
BCH04545 (D2) BEST CHOICE TATER PUFFS 6X4LB
BCI00255 BREW CITY 5/16 SC 6X5
 
The SKU is easy: Trim(Left([YourField], InStr([YourField], "-") - 1))

What's after, though, is a little hairier. To get what's after the dash,
you'd use

Trim(Mid([YourField], InStr([YourField], "-") + 1))

However, you'd need to check whether there's a closing parenthesis in that,
and split again if so. That means you could get the Discontinued field as

IIf(InStr(Trim(Mid([YourField], InStr([YourField], "-") + 1)), ")") > 0,
Left(Trim(Mid([YourField], InStr([YourField], "-") + 1)),
InStr(Trim(Mid([YourField], InStr([YourField], "-") + 1)), ")"))), Null)

and the SKU Description as

IIf(InStr(Trim(Mid([YourField], InStr([YourField], "-") + 1)), ")") > 0,
Left(Trim(Mid([YourField], InStr([YourField], "-") + 1)),
InStr(Trim(Mid([YourField], InStr([YourField], "-") + 1)), ")"))),
Trim(Mid([YourField], InStr([YourField], "-") + 1)))

I THINK I've got the parentheses correct there. Hopefully you can see how
hairy it is, so that it might make sense to write 3 functions, one to return
each part.
 
Back
Top