Split Field data into Table Columns

Posted on 2011-10-05
Last Modified: 2012-05-12
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 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?
Question by:correlate
    LVL 9

    Expert Comment

    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)
    LVL 47

    Accepted Solution

    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


    Author Comment

    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?
    LVL 9

    Expert Comment

    @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


    Author Comment

    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?
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    oops, don't know how I missed that comma, should beL

    fnParse([Content], 3, "~")
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    and yes, if you change the default in the function you can drop the last parameter from each of the function calls.

    Author Closing Comment

    Absolute genius - thank you very much
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    You're welcome

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now