Splitting Text in a Field Based on a Delimiter

[silva]

I have imported text from a generated CSV file. However, in one field I have
two pieces of information that need to be seperated out separated by the
following delimiter: "-". The problem that I have is that there is no set
amount of characters before reaching the delimiter. I had planned to write a
pair of functions that would grab either the first or second piece of data
and call the function within a query, but I have no idea what methods could
be used in order to scope out that delimiter. I had been using Mid() for
something else, but the delimiter is always found in one of two places for
that field, so I don't think it would work in this case (unless there's a
function that'll return the character position value in a string). Any ideas?

 

[Jeff Boyce]

A couple possibilities, depending on the version of Access you are using...

In all versions, take a look at the InStr() function. Use it to "find" the
location of that character, then use that information to parse the
concatenated information into two fields, perhaps something like:

PartOne: Left([YourInputField], InStr([YourInputField], "-")-1)
PartTwo: Mid([YourINputField],InStr([YourInputField], "-")+1)

Or, if you're using a newer version, look for the Split() function.

If neither of these approaches work, consider using Parse() in Excel before
pulling the data into Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[silva]

Thank you! That is exactly what I needed and it works just how I want. I knew
it had to be something simple like that, but I wasn't sure what
function/method to search. Very much appreciated.

A couple possibilities, depending on the version of Access you are using...

In all versions, take a look at the InStr() function. Use it to "find" the
location of that character, then use that information to parse the
concatenated information into two fields, perhaps something like:

PartOne: Left([YourInputField], InStr([YourInputField], "-")-1)
PartTwo: Mid([YourINputField],InStr([YourInputField], "-")+1)

Or, if you're using a newer version, look for the Split() function.

If neither of these approaches work, consider using Parse() in Excel before
pulling the data into Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have imported text from a generated CSV file. However, in one field I
have
two pieces of information that need to be seperated out separated by the
following delimiter: "-". The problem that I have is that there is no set
amount of characters before reaching the delimiter. I had planned to write
a
pair of functions that would grab either the first or second piece of data
and call the function within a query, but I have no idea what methods
could
be used in order to scope out that delimiter. I had been using Mid() for
something else, but the delimiter is always found in one of two places for
that field, so I don't think it would work in this case (unless there's a
function that'll return the character position value in a string). Any
ideas?