Split Column into Multiple Columns

I have a table in Microsoft Access that collects survey results.
All the contents of the survay are collected in one column in the same cell one below each other.
I need to split the results into separate columns.
Each result could have multiple character length.
Number five could have up to 256 characters.
I know there is a function called (InStr).
Example of the data that shows up in one column below.
Could you please give me an example for this query argument.


1, 2Yr

2, No

3, Yes

4, No

5, Test Msg

submit, Submit
Who is Participating?
Dale FyeConnect With a Mentor Commented:
I created a table (tbl_Memo) and put two fields ID and MemoCol into the table.  I then copied the text from your original post and pasted it into MemoCol.  Assuming that you have two CRLF characters after each question, the following query works to identify the answers for the first three questions in the sample data that I created, but without your exact data, I won't know for sure.

SELECT tbl_Memo.MemoCol
           , fnParseText3([MemoCol],"1",Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10)) AS Q1
           , fnParseText3([MemoCol],"2",Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10)) AS Q2
           , fnParseText3([MemoCol],"3",Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10)) AS Q3
FROM tbl_Memo;

The function fnParseText3 looks like:
Public Function fnParseText3(SomeText As Variant, StartsWith As String, Optional Delimiter As String = ",") As Variant

    Dim strArray() As String
    Dim intLoop As Integer
    'define the default return value as NULL
    fnParseText3 = Null
    'If SomeText is NULL then pass NULL back to the calling routine
    If IsNull(SomeText) Or StartsWith = "" Then
        'return the default value
        'Split the string into array elements on the delimiter
        strArray = Split(SomeText, Delimiter)
        'if the requested segment is greater than the number of elements in the array, return NULL
        For intLoop = LBound(strArray) To UBound(strArray)
            If InStr(strArray(intLoop), StartsWith) = 1 Then
                fnParseText3 = Mid(strArray(intLoop), InStr(strArray(intLoop), ",") + 1)
                Exit Function
            End If
    End If
End Function

Open in new window

Dale FyeCommented:
Take a look at my response to this very similar post:
dosuserAuthor Commented:
Gave it a try and could not get it to work with, In a query, use: Mid([Memfield],InStr(1,[Memfield],"Material =")+11,InStr(1,[Memfield],"Other =")-InStr(1,[Memfield],"Material =")-11)
I changed all memfield and string like "Other =" to correct field or chr.  What I have that will give the first string correct is, expr1: IIf([contents] Like "* *",Left([contents],InStr([contents],"_")+10))
Tryed this but it did't work eather,  expr2: IIf([contents] Like "* *",Left([contents],InStr([contents],"5")-InStr(1,[contents],"3")+10))
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Dale FyeCommented:
If you do it this way, it is going to be a nightmare.  Could you copy a couple of records from your table into a new database and post it here?  I'll take a look for you.
Jeffrey CoachmanMIS LiasonCommented:
<All the contents of the survay are collected in one column in the same cell one below each other.>
Then it seems that you really need to fix ("Normalize") your data.

In a normal system a record will contain multiple field, ...so I am a bit confused as to how you managed to get data formatted like this at all...?
(Line breaks, ...ect)

What is:
<submit, Submit>

An example of a typical survey design might be:



Then by joining (relating) the tables you can group all the answers by the question and respondent.
*Note* that this is an extremely simplified structure.
Your actual survey may be quite different (in both what it is, and what it might need to be)

But stay with fyed, he has a good success rate with challenges like this.


Jeffrey CoachmanMIS LiasonCommented:
You go boy!
dosuserAuthor Commented:
This was a good solution for the stated problem. Quite inventive. Once I got it figured out from the code and that I was supposed to build a public function. No problem worked like a champ. Thanks again for all the help!!
Dale FyeCommented:
Glad I could help.
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.

All Courses

From novice to tech pro — start learning today.