If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
left "_" & "-"
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.... -- GS |
#2
|
|||
|
|||
left "_" & "-"
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) -- KARL DEWEY Build a little - Test a little "Gary F Shelton" wrote: 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.... -- GS |
#3
|
|||
|
|||
left "_" & "-"
Gary F Shelton wrote:
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.... 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 -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" |
#4
|
|||
|
|||
left "_" & "-"
On Tue, 8 Jul 2008 13:54:03 -0700, Gary F Shelton wrote:
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.... CustNumber:Val([FieldName]) Extension:Val(Mid([FieldName],Instr([FieldName],"_")+1)) Description:Mid([FieldName],InStr([FieldName],"-")+2) -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
#5
|
|||
|
|||
left "_" & "-"
Gary F Shelton wrote:
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.... 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. -- Marsh MVP [MS Access] |
#6
|
|||
|
|||
left "_" & "-"
Thank you everyone ... I got it to work with your suggestions...
-- GS "Marshall Barton" wrote: Gary F Shelton wrote: 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.... 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. -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|