separate a single field into multiple fields

[Guest]

does anyone know an easy way to split the data of an imported table? By this
I mean the following will pull data into one filed. " FullName:
Trim(Personnel.Rank & " " & [LName]) " I have come across a situation where I
need to separate a single field into multiple fields.

Here is some of the sample data. I have the tables linked to a text file and
am importing the data to table with an update query. I need to separate the
data in Laddawn Field 2 as follows LF, 2, 3, 0015, 1000, ctn and Box
Distributor will be similar for the description column. Any suggestions?

LADDAWN-P1-111705 Field1 Field2 Field3 Field4 Field5 Field6
5 LF 2X3X0015 1000/CTN CS 1000 1 5.09



Box Distributor Description SN Price UM Pack/Size
4 x 4 x 4 R.S.C. 444 $165.32 M 25/1800

 

[John Nurick]

Hi Darrin,

Your two sample records are so different that it's not possible to
generalise from them.

Even if both fitted the same pattern there's no "easy way" to split data
like this: you have to use your knowledge of the pattern to create a
separate expression or custom procedure to extract each of the
substrings you need from the field in question.

does anyone know an easy way to split the data of an imported table? By this
I mean the following will pull data into one filed. " FullName:
Trim(Personnel.Rank & " " & [LName]) " I have come across a situation where I
need to separate a single field into multiple fields.

Here is some of the sample data. I have the tables linked to a text file and
am importing the data to table with an update query. I need to separate the
data in Laddawn Field 2 as follows LF, 2, 3, 0015, 1000, ctn and Box
Distributor will be similar for the description column. Any suggestions?

LADDAWN-P1-111705 Field1 Field2 Field3 Field4 Field5 Field6
5 LF 2X3X0015 1000/CTN CS 1000 1 5.09



Box Distributor Description SN Price UM Pack/Size
4 x 4 x 4 R.S.C. 444 $165.32 M 25/1800

 

[Guest]

I did find out this can be done with the substring function to break the
field down but I need an example to work from. This is a sample of a good
portion of the data I need to break down.

LF 2X3X0015 1000/CTN

Hi Darrin,

Your two sample records are so different that it's not possible to
generalise from them.

Even if both fitted the same pattern there's no "easy way" to split data
like this: you have to use your knowledge of the pattern to create a
separate expression or custom procedure to extract each of the
substrings you need from the field in question.

does anyone know an easy way to split the data of an imported table? By this
I mean the following will pull data into one filed. " FullName:
Trim(Personnel.Rank & " " & [LName]) " I have come across a situation where I
need to separate a single field into multiple fields.

Here is some of the sample data. I have the tables linked to a text file and
am importing the data to table with an update query. I need to separate the
data in Laddawn Field 2 as follows LF, 2, 3, 0015, 1000, ctn and Box
Distributor will be similar for the description column. Any suggestions?

LADDAWN-P1-111705 Field1 Field2 Field3 Field4 Field5 Field6
5 LF 2X3X0015 1000/CTN CS 1000 1 5.09



Box Distributor Description SN Price UM Pack/Size
4 x 4 x 4 R.S.C. 444 $165.32 M 25/1800

 

[John Nurick]

Well, if you want to get the first two characters from the string you
can use this, (replacing XXX with the name of the field, assuming you're
workiing in a query):

Left([XXX], 2)

Fourth character:
Mid([XXX], 4, 1)

The character after the first "X":
Mid([XXX], Instr([XXX], "X") + 1, 1)

The four characters after the second "X":
Mid([XXX], Instr(InStr([XXX], "X") + 1, [XXX], "X" ) + 1, 4)

The four characters before the last "/":
Mid([XXX], InstrRev([XXX], "/") - 4, 4)

I hope this gets you started.

I did find out this can be done with the substring function to break the
field down but I need an example to work from. This is a sample of a good
portion of the data I need to break down.

LF 2X3X0015 1000/CTN

Hi Darrin,

Your two sample records are so different that it's not possible to
generalise from them.

Even if both fitted the same pattern there's no "easy way" to split data
like this: you have to use your knowledge of the pattern to create a
separate expression or custom procedure to extract each of the
substrings you need from the field in question.

does anyone know an easy way to split the data of an imported table? By this
I mean the following will pull data into one filed. " FullName:
Trim(Personnel.Rank & " " & [LName]) " I have come across a situation where I
need to separate a single field into multiple fields.

Here is some of the sample data. I have the tables linked to a text file and
am importing the data to table with an update query. I need to separate the
data in Laddawn Field 2 as follows LF, 2, 3, 0015, 1000, ctn and Box
Distributor will be similar for the description column. Any suggestions?

LADDAWN-P1-111705 Field1 Field2 Field3 Field4 Field5 Field6
5 LF 2X3X0015 1000/CTN CS 1000 1 5.09



Box Distributor Description SN Price UM Pack/Size
4 x 4 x 4 R.S.C. 444 $165.32 M 25/1800

 

[Guest]

Thanks I will give it a shot.

Well, if you want to get the first two characters from the string you
can use this, (replacing XXX with the name of the field, assuming you're
workiing in a query):

Left([XXX], 2)

Fourth character:
Mid([XXX], 4, 1)

The character after the first "X":
Mid([XXX], Instr([XXX], "X") + 1, 1)

The four characters after the second "X":
Mid([XXX], Instr(InStr([XXX], "X") + 1, [XXX], "X" ) + 1, 4)

The four characters before the last "/":
Mid([XXX], InstrRev([XXX], "/") - 4, 4)

I hope this gets you started.

I did find out this can be done with the substring function to break the
field down but I need an example to work from. This is a sample of a good
portion of the data I need to break down.

LF 2X3X0015 1000/CTN

Hi Darrin,

Your two sample records are so different that it's not possible to
generalise from them.

Even if both fitted the same pattern there's no "easy way" to split data
like this: you have to use your knowledge of the pattern to create a
separate expression or custom procedure to extract each of the
substrings you need from the field in question.

On Mon, 3 Apr 2006 17:26:01 -0700, Darrin

does anyone know an easy way to split the data of an imported table? By this
I mean the following will pull data into one filed. " FullName:
Trim(Personnel.Rank & " " & [LName]) " I have come across a situation where I
need to separate a single field into multiple fields.

Here is some of the sample data. I have the tables linked to a text file and
am importing the data to table with an update query. I need to separate the
data in Laddawn Field 2 as follows LF, 2, 3, 0015, 1000, ctn and Box
Distributor will be similar for the description column. Any suggestions?

LADDAWN-P1-111705 Field1 Field2 Field3 Field4 Field5 Field6
5 LF 2X3X0015 1000/CTN CS 1000 1 5.09



Box Distributor Description SN Price UM Pack/Size
4 x 4 x 4 R.S.C. 444 $165.32 M 25/1800