Identifying single values in a field that stores muliple valuesseparated by a comma

[Lina]

Hello,
I am converting data and trying to split up a field that stores
multiple values separated by a comma into separate fields

Example: Field 1 will have the following data "A, B, C" or "A, B"
I need to update Field 2 with "A", Field 3 with "B" and Field 4 with
"C"

For Field 2, I tried this: Left([Field1],InStr([Field1],",")) but this
displays the value as "A," - I do not need the comma displayed

For Field 3, I tried this: Right([Field1],Len([Field1])-
InStr([Field1],",")) but this displays the value as "[space]B, C" and
i want to only display "B" no space

Can anyone help?

Thanks!

 

[OssieMac]

Hi Lina,

I don't know if there is a way of doing this in Access but as it is a Comma
Separated Values file then I would open it in Excel which has an inbuilt
feature (Text to columns) to separate the values in .csv files.

Save the file as a NORMAL Excel file then in Access you can then use File ->
Get External Data -> Import

Excel will open .csv files and .txt files and parse them. If it is a .csv
file then Excel automatically parses them on open. If it is a .txt file then
you get the Text to Columns dialog boxes where you can control some features
of the import and tell Excel what the delimiter is (in your case comma) and
also tell it if fields are dates etc. I often change the name of .csv to .txt
so I can use the Text to Columns dialog boxes feature if the import is not
occurrring as I require it.

 

[OssieMac]

Hi again Lina,

If the data is already a table in Access and not coming from a text file
then you can Export the table to Excel then simply open the Excel file and
insert the number of required columns to the right of the one to be parsed
and then select Text To columns and manually parse the data. Resave the file
and import it back to Access. If you import it to a NEW table in access you
don't have to worry about getting the column headers in both the Excel and
Access table matching because it will do it for you.

 

[vbasean]

try this:
create a custom function in a module:
Function splitmyfield(str As String, index As Integer) As String
Dim ar
ar = Split(str, ",")
splitmyfield = ar(index)
End Function

create an update query using your custom function:
UPDATE Table1 SET Table1.Name1 = splitmyfield([names],0), Table1.Name2 =
splitmyfield([names],1), Table1.Name3 = splitmyfield([names],2);

the split function in the custom function creates a zero based array which
can be passed back as in your update query

i.e. splitmyfield([the name of the field to split], [the index / place the
new field lies in your comma delimited field])

 

[KARL DEWEY]

Left([Field1],InStr([Field1],",")-1) drops the comma.

Mid([Field1],InStr([Field1],",")+2,InStr(Mid([Field1],InStr([Field1],",")-1)) for B

If you only have 3 sets of data then the last like this --
Mid([Field1], InStrRev([Field1], ",")+2, 99)

 

[Lina]

Left([Field1],InStr([Field1],",")-1)   drops the comma.

Mid([Field1],InStr([Field1],",")+2,InStr(Mid([Field1],InStr([Field1],",")-1­))   for B

If you only have 3 sets of data then the last like this --
Mid([Field1], InStrRev([Field1], ",")+2, 99)

--
KARL DEWEY
Build a little - Test a little

Hello,
I am converting data and trying to split up a field that stores
multiple values separated by a comma into separate fields

Example: Field 1 will have the following data "A, B, C" or "A, B"
I need to update Field 2 with "A", Field 3 with "B" and Field 4 with
"C"

For Field 2, I tried this: Left([Field1],InStr([Field1],",")) but this
displays the value as "A,"  - I do not need the comma displayed

For Field 3, I tried this: Right([Field1],Len([Field1])-
InStr([Field1],",")) but this displays the value as "[space]B, C" and
i want to only display "B" no space

Can anyone help?

Thanks!- Hide quoted text -

- Show quoted text -

Thank you veryone for your help!
Mid([Field1],InStr([Field1],",")
+2,InStr(Mid([Field1],InStr([Field1],",")-1­)) - returns #Error,
what am i missing?

 

[vbasean]

actually, my function would fail if you only had two instead of three items
AND if you have your field set to not allow empty strings there would have
to be some error catching SO here's the correct function.

Function splitmyfield(str As String, index As Integer) As Variant
On Error GoTo SendNull

Dim ar
ar = Split(str, ",")
splitmyfield = ar(index)

SendNull:
splitbyfield = Null
Resume Next

End Function

try this:
create a custom function in a module:
Function splitmyfield(str As String, index As Integer) As String
Dim ar
ar = Split(str, ",")
splitmyfield = ar(index)
End Function

create an update query using your custom function:
UPDATE Table1 SET Table1.Name1 = splitmyfield([names],0), Table1.Name2 =
splitmyfield([names],1), Table1.Name3 = splitmyfield([names],2);

the split function in the custom function creates a zero based array which
can be passed back as in your update query

i.e. splitmyfield([the name of the field to split], [the index / place the
new field lies in your comma delimited field])

Hello,
I am converting data and trying to split up a field that stores
multiple values separated by a comma into separate fields

Example: Field 1 will have the following data "A, B, C" or "A, B"
I need to update Field 2 with "A", Field 3 with "B" and Field 4 with
"C"

For Field 2, I tried this: Left([Field1],InStr([Field1],",")) but this
displays the value as "A," - I do not need the comma displayed

For Field 3, I tried this: Right([Field1],Len([Field1])-
InStr([Field1],",")) but this displays the value as "[space]B, C" and
i want to only display "B" no space

Can anyone help?

Thanks!

 

[Clif McIrvin]

Left([Field1],InStr([Field1],",")-1) drops the comma.

Mid([Field1],InStr([Field1],",")+2,InStr(Mid([Field1],InStr([Field1],",")-1­))
for B

If you only have 3 sets of data then the last like this --
Mid([Field1], InStrRev([Field1], ",")+2, 99)

--
KARL DEWEY
Build a little - Test a little

Hello,
I am converting data and trying to split up a field that stores
multiple values separated by a comma into separate fields

Example: Field 1 will have the following data "A, B, C" or "A, B"
I need to update Field 2 with "A", Field 3 with "B" and Field 4 with
"C"

For Field 2, I tried this: Left([Field1],InStr([Field1],",")) but
this
displays the value as "A," - I do not need the comma displayed

For Field 3, I tried this: Right([Field1],Len([Field1])-
InStr([Field1],",")) but this displays the value as "[space]B, C"
and
i want to only display "B" no space

Can anyone help?

Thanks!- Hide quoted text -

- Show quoted text -

Thank you veryone for your help!
Mid([Field1],InStr([Field1],",")
+2,InStr(Mid([Field1],InStr([Field1],",")-1­)) - returns #Error,
what am i missing?

Try this:

the mid function is (string, start, length), and
instr is (start, string1, string2), so:

Mid([Field1], InStr([Field1],",")+1, InStr(Mid([Field1]
,InStr([Field1],",")+1­) - InStr([Field1],",")+1) for B

If you only have 3 sets of data then the last like this --
Mid([Field1], InStrRev([Field1], ",")+1,99)

you have to find both the first and second comma to break out field B
and calculate the length. I hope I got the functions right.

The 99 assumes that Field C will always be shorter than 99 characters.

I'm pretty certain this will break if there aren't 3 values in every
field.

OssieMac gave you a brute force method that will work quite well if this
is a one time operation.

vbasean gave you a much cleaner (and easier to maintain) solution than
this one.

I'm not sure why Karl had +2; seems to me that would drop the first
character of Field B.

Good Luck!

 

[Lina]

Left([Field1],InStr([Field1],",")-1) drops the comma.

Mid([Field1],InStr([Field1],",")+2,InStr(Mid([Field1],InStr([Field1],",")-1­­))
for B

If you only have 3 sets of data then the last like this --
Mid([Field1], InStrRev([Field1], ",")+2, 99)

Hello,
I am converting data and trying to split up a field that stores
multiple values separated by a comma into separate fields
Example: Field 1 will have the following data "A, B, C" or "A, B"
I need to update Field 2 with "A", Field 3 with "B" and Field 4 with
"C"
For Field 2, I tried this: Left([Field1],InStr([Field1],",")) but
this
displays the value as "A," - I do not need the comma displayed
For Field 3, I tried this: Right([Field1],Len([Field1])-
InStr([Field1],",")) but this displays the value as "[space]B, C"
and
i want to only display "B" no space
Can anyone help?
Thanks!- Hide quoted text -

- Show quoted text -

Thank you veryone for your help!
Mid([Field1],InStr([Field1],",")
+2,InStr(Mid([Field1],InStr([Field1],",")-1­))   - returns #Error,
what am i missing?

Try this:

the mid function is (string, start, length), and
instr is (start, string1, string2), so:

Mid([Field1], InStr([Field1],",")+1, InStr(Mid([Field1]
,InStr([Field1],",")+1­) - InStr([Field1],",")+1) for B

If you only have 3 sets of data then the last like this --
Mid([Field1], InStrRev([Field1], ",")+1,99)

you have to find both the first and second comma to break out field B
and calculate the length. I hope I got the functions right.

The 99 assumes that Field C will always be shorter than 99 characters.

I'm pretty certain this will break if there aren't 3 values in every
field.

OssieMac gave you a brute force method that will work quite well if this
is a one time operation.

vbasean gave you a much cleaner (and easier to maintain) solution than
this one.

I'm not sure why Karl had +2; seems to me that would drop the first
character of Field B.

Good Luck!
--
Clif
Still learning Access 2003- Hide quoted text -

- Show quoted text -

Thank you everyone!!!!

 

[KARL DEWEY]

Try this as it test to see if there is a comma present at all and following
the first comma --
Expr1:
IIf(InStr([Field1],",")=0,"",IIf(InStr(Mid([Field1],InStr([Field1],",")-1),",")=0,"",Mid([Field1],InStr([Field1],",")+2,InStr(Mid([Field1],InStr([Field1],",")-1),",")-1)))

--
KARL DEWEY
Build a little - Test a little

Left([Field1],InStr([Field1],",")-1) drops the comma.

Mid([Field1],InStr([Field1],",")+2,InStr(Mid([Field1],InStr([Field1],",")-1­)) for B

If you only have 3 sets of data then the last like this --
Mid([Field1], InStrRev([Field1], ",")+2, 99)

--
KARL DEWEY
Build a little - Test a little

Hello,
I am converting data and trying to split up a field that stores
multiple values separated by a comma into separate fields

Example: Field 1 will have the following data "A, B, C" or "A, B"
I need to update Field 2 with "A", Field 3 with "B" and Field 4 with
"C"

For Field 2, I tried this: Left([Field1],InStr([Field1],",")) but this
displays the value as "A," - I do not need the comma displayed

For Field 3, I tried this: Right([Field1],Len([Field1])-
InStr([Field1],",")) but this displays the value as "[space]B, C" and
i want to only display "B" no space

Can anyone help?

Thanks!- Hide quoted text -

- Show quoted text -

Thank you veryone for your help!
Mid([Field1],InStr([Field1],",")
+2,InStr(Mid([Field1],InStr([Field1],",")-1­)) - returns #Error,
what am i missing?

 

[John W. Vinson]

Hello,
I am converting data and trying to split up a field that stores
multiple values separated by a comma into separate fields

Example: Field 1 will have the following data "A, B, C" or "A, B"
I need to update Field 2 with "A", Field 3 with "B" and Field 4 with
"C"

Ummm.... No. That's moving your database design from dreadful to very bad, if
it is indeed as you describe.

If you have a one-to-many relationship you need *TWO TABLES*, with one
*record*, not one field, per value.

What are the actual fields? What type of data does this table represent and
what are the actual values in this field?

For Field 2, I tried this: Left([Field1],InStr([Field1],",")) but this
displays the value as "A," - I do not need the comma displayed

For Field 3, I tried this: Right([Field1],Len([Field1])-
InStr([Field1],",")) but this displays the value as "[space]B, C" and
i want to only display "B" no space

Can anyone help?

If the number of values is arbitrary, you'll need some VBA code using the
Split() function to split the string into "tokens". See the VBA help for Split
and/or post back.