Split Field data into Table Columns

Dear Experts

I need to write a piece of code which splits out the data contained in the field “Contents” and either creates a new table with the data split out or appends the data to an existing table (TblOutput) in the relevant field

(Hopefully this makes sense - if it was in Excel this would be a Data>Text to Column action)

The data in the field “Contents” is separated by a “~” and looks like this:

Weekly Update~example@hotmail.com~Ima Example~Example Co~Head of Exampling.  

The Table (TblOutput) has 5 fields:


The data need to get mapped as follows

“Content”                Field Name in TblOutput
1st Section      =      List
2nd Section     =     Email
3rd Section      =      Name
4th Section      =      Firm
5th Section      =      Role

And should end up looking like this in the table:

 End Result

Can anybody Help?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Assuming you know the target fields I would suggest you to use two built-in Access functions to do this:

InstrRev(stringcheck, stringmatch[, start[, compare]]) - returns the position of an occurrence of one string within another, from the end of string.
stringcheck - string being searched
stringmatch - string that we're looking for
Mid(string, start[, length]) - returns a string containing a specified number of characters from a string.
string - string to work with
start - where to start

So far example I have to split full name into first name and last name I would do the following to extract the last name
Mid([full_name],InstrRev([full_name]," ") + 1)
Dale FyeCommented:
Personally, I prefer Split the Split() function, which will parse a text string on a particular field into an array.  But to use it in a query, you would need to wrap it in a function call.

To use this in a query, the syntax would be:

SELECT fnParse([Content], 1, "~") as List, fnParse([Content], 2, "~") as Email, fnParse([Content], 3 "~") as Name,fnParse([Content], 4, "~") as Firm,fnParse([Content], 4, "~") as Role
FROM yourtable
WHERE instr([Content], "~") > 0

Public Function fnParse(TextToParse As String, Position As Integer, Optional Delimiter As String = " ") As String

    Dim strArray() As String
    strArray = Split(TextToParse, Delimiter)
    If Position > UBound(strArray) + 1 Then
        fnParse = ""
        fnParse = strArray(Position - 1)
    End If
End Function

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
correlateAuthor Commented:
Hi Pritamdutt,

Thanks for this, just so i'm on the right track your Mid([full_name],InstrRev([full_name]," ") + 1) does the following ... reading left to right across the field, the InstrRev bit looks for the seperator (in your case a space, in mine a ~) & the mid piece takes everything after the seperator.   If that right then any ideas how I can get the it to look at the 2nd, 3rd etc seperators?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

@correlate  your question has brought me to a conclusion to use the following method:
Press ALT+F11 and
1. Create a VBA "Split" function the way it has been suggested by fyed
2. Make a call to function in your query already suggested by fyed

correlateAuthor Commented:
Hy Fyed,

Thanks for this - I am getting an error message....

I have also tried it having changed the function code to read
..... Optional Delimiter As String = "~") As String rather than string, but still got the same error

Any ideas?
Dale FyeCommented:
oops, don't know how I missed that comma, should beL

fnParse([Content], 3, "~")
Dale FyeCommented:
and yes, if you change the default in the function you can drop the last parameter from each of the function calls.
correlateAuthor Commented:
Absolute genius - thank you very much
Dale FyeCommented:
You're welcome
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.