left "_" & "-"

G

Gary F Shelton

Here is some of my data that I want to unconcatonate... Need some help with
doing this as you can see the data length changes with each change of account.

1300_1 - CASH WA DISTRIBUTING
337100_1 - NORTH CENTER FOOD SERVICE
380009_1 - C & M FOOD
7258_1 - REINHART LACROSSE
216000_44 - SNOW FRESH APPETIZERS
3676_16 - GFS GREEN OAK DC
704125_10 - DOT FOODS

First numeric digits are a customer number, then the one or two digit
customer extension, and then the customer description....
 
K

KARL DEWEY

Try these ---
Customer: Left([YourField], InStr([YourField], "_")-1)
Extention: Left(Mid([YourField],InStr([YourField], "_")+1),
InStr([YourField], " ")-1)
Description: Right([YourField], Len([YourField]) - InStr([YourField], " ")+2)
 
B

Bob Barrows [MVP]

Gary said:
Here is some of my data that I want to unconcatonate... Need some
help with doing this as you can see the data length changes with each
change of account.

1300_1 - CASH WA DISTRIBUTING
337100_1 - NORTH CENTER FOOD SERVICE
380009_1 - C & M FOOD
7258_1 - REINHART LACROSSE
216000_44 - SNOW FRESH APPETIZERS
3676_16 - GFS GREEN OAK DC
704125_10 - DOT FOODS

First numeric digits are a customer number, then the one or two digit
customer extension, and then the customer description....
Click to expand...

It would have helped if you had shown us the desired output for each row of
that sample data, but I think I can see that _ is used to separate the
customer number from the extension, and " - " is used to separate the
description.

It is possible to create a lengthy, difficult-to-maintain, expression that
uses InStr, Len, Mid, etc. to do this, but if I were doing it, I would write
a VBA function in a module, like this:

Public Function ParseCustString(sInput as string, _
iPortion as Integer) as String
dim aSplit as variant, s as string
Select Case iPortion
Case 1
'Customer Number
aSplit = Split(sInput,"_")
ParseCustString=aSplit(0)
Case 2
'Customer Extension
aSplit = Split(sInput,"_")
s=aSplit(1)
aSplit = Split(s," - ")
ParseCustString=aSplit(0)
Case 3
'Customer Description
aSplit = Split(sInput," - ")
ParseCustString=aSplit(1)
End Select
End Function

To use it:

Select ParseCustString([fieldname],1) As CustomerNumber,
ParseCustString([fieldname],2) As CustomerExtension,
ParseCustString([fieldname],3) As CustomerDescription
From tablename
 
F

fredg

Here is some of my data that I want to unconcatonate... Need some help with
doing this as you can see the data length changes with each change of account.

1300_1 - CASH WA DISTRIBUTING
337100_1 - NORTH CENTER FOOD SERVICE
380009_1 - C & M FOOD
7258_1 - REINHART LACROSSE
216000_44 - SNOW FRESH APPETIZERS
3676_16 - GFS GREEN OAK DC
704125_10 - DOT FOODS

First numeric digits are a customer number, then the one or two digit
customer extension, and then the customer description....
Click to expand...

CustNumber:Val([FieldName])
Extension:Val(Mid([FieldName],Instr([FieldName],"_")+1))
Description:Mid([FieldName],InStr([FieldName],"-")+2)
 
M

Marshall Barton

Gary said:
Here is some of my data that I want to unconcatonate... Need some help with
doing this as you can see the data length changes with each change of account.

1300_1 - CASH WA DISTRIBUTING
337100_1 - NORTH CENTER FOOD SERVICE
380009_1 - C & M FOOD
7258_1 - REINHART LACROSSE
216000_44 - SNOW FRESH APPETIZERS
3676_16 - GFS GREEN OAK DC
704125_10 - DOT FOODS

First numeric digits are a customer number, then the one or two digit
customer extension, and then the customer description....
Click to expand...


Use Sub procedure with the InStr function (see VBA Help) to
first locate the _ then the " - ". You can then use the
Left, Righr and Mid functions to get the separate parts.
 
Top