Split Field data into Table Columns

Posted on 2011-10-05
Medium Priority
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 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?
Question by:correlate
  • 4
  • 3
  • 2

Expert Comment

ID: 36916329
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 49

Accepted Solution

Dale Fye earned 2000 total points
ID: 36916380
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

ID: 36916393
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?
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database


Expert Comment

ID: 36916444
@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

ID: 36916458
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 49

Expert Comment

by:Dale Fye
ID: 36916475
oops, don't know how I missed that comma, should beL

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

Expert Comment

by:Dale Fye
ID: 36916485
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

ID: 36916511
Absolute genius - thank you very much
LVL 49

Expert Comment

by:Dale Fye
ID: 36916529
You're welcome

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses
Course of the Month13 days, 22 hours left to enroll

807 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