force formatting imported text file in excel

I've got text file containing MCQ quizzes. Currently I have to edit all the quiz questions with delimiters (e.g TABS) before importing them into excel.

I need an automated way to format the imported file into these columns:

QuestionId, ExamType, Year, Subject, Question, Answer1, Answer2, Answer3, Answer4, Answer5, CorrectAnswer, Image 

without having to manually edit the text first.

Here's an example of the text I'm currently importing into excel.

1.Government as an art of governing refers to the process of A.ruling people in the society b.establishing political parties C. providing free education D. acquiring social skills, 2. An essential feature of a State is A. availability of mineral resources B. developed infrastructure. C an organized system of laws D. developed markets.

I'd like to fit example 1 and 2 into the columns above. I have zipped up what I've been doing so that you can have a look. I also included the raw quiz data so that you can have an idea what it is that i'm trying to force format

Try This:

Open up your excel document and hit <kbd>Alt</kbd>+<kbd>F11</kbd> to bring up the VBA editor, insert a new module (if one doesn't already exist), open it up, and paste in the following code (they're custom user defined functions that we'll use in a little bit)

Function LEFTDELIMIT(ByVal text As String, ByVal delimiter As String)
    Dim position As Integer
    Dim leftText As String
    position = InStr(1, text, delimiter, vbTextCompare) - 1
    leftText = Left(text, position)
    LEFTDELIMIT = leftText
End Function

Function RIGHTDELIMIT(text, delimiter)
    Dim position As Integer
    Dim rightText As String
    position = Len(text) - Len(delimiter) - InStr(1, text, delimiter, vbTextCompare) + 1
    rightText = Right(text, position)
    RIGHTDELIMIT = rightText
End Function

Function NOERROR(text)
    If IsError(text) Then
        NOERROR = ""
    Else
        NOERROR = text
    End If
End Function

I'm guessing at this point that all of the text for your quizzes is in A1. Go ahead and delimit that cell by commas as I specified earlier to get each question in its own column. Since we want each of those questions to occupy its own row, highlight all of row 1 and copy and then paste special into A2 and select the option to transpose the values. Now each question has it's own row. Now what we'd like to do it give each answer choice its own column. We can use the custom functions from before which allow you to get all the text to the left or right of a custom delimiter.

If the value of A2 is

• 1.Government as an art of governing refers to the process of A.ruling people in the society b.establishing political parties C. providing free education D. acquiring social skills

Then we'll fill out the other columns with the following code:

• B2: =LEFTDELIMIT(A2,"A.")
• C2: =NOERROR(PROPER(TRIM(LEFTDELIMIT(RIGHTDELIMIT(A2,"A."),"B."))))
• D2: =NOERROR(PROPER(TRIM(LEFTDELIMIT(RIGHTDELIMIT(A2,"B."),"C."))))
• E2: =NOERROR(PROPER(TRIM(LEFTDELIMIT(RIGHTDELIMIT(A2,"C."),"D."))))
• F2: =NOERROR(PROPER(TRIM(LEFTDELIMIT(RIGHTDELIMIT(A2,"C."),"D."))))
• G2: =NOERROR(PROPER(TRIM(LEFTDELIMIT(RIGHTDELIMIT(A2,"D."),"E."))))

This is making certain assumptions about how the incoming data is formatted. If this doesn't work, please show all of you work in an excel file that you can upload and distribute through any online file sharing host to get a better look at what particular errors might be tripping you up