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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Dale FyeOwner, Developing Solutions LLCCommented:
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))
Dale FyeOwner, Developing Solutions LLCCommented:
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.
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

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.


Dale FyeOwner, Developing Solutions LLCCommented:
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


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
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 FyeOwner, Developing Solutions LLCCommented:
Glad I could help.
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 Applications

From novice to tech pro — start learning today.