asked on

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:

List
Email
Name
Firm
Role

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:

Can anybody Help?

pritamdutt

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)

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tom Crowfoot

ASKER

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?

pritamdutt

@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

Tom Crowfoot

ASKER

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 Fye

oops, don't know how I missed that comma, should beL

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

Dale Fye

and yes, if you change the default in the function you can drop the last parameter from each of the function calls.

Tom Crowfoot

ASKER

Absolute genius - thank you very much

Dale Fye

You're welcome