Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Currently, I am working on a column with the content for teacher's comment.

I would like to delimit it by the space or comma or punctuation marks between words inside the content.

At the same time, I would like to take out each of the words in the content and then place them into rows instead of columns .


Catherine is a good girl . -> This is the teacher's comment.

After delimit in excel, I have "Catherine is a good girl . " in 6 columns with 1 column for 1 word.

However, I would like the outcome of the words to be placed into rows. It would look like this, in 6 rows:

share|improve this question

Excel has the ability to do that.

  1. Copy the data in one or more columns or rows.

  2. Before you paste the copied data, right-click your first destination cell (the first cell of the row or column into which you want to paste your data), and then click Paste Special.

  3. In the Paste Special dialog box, select Transpose, and then click OK.

You'll find theTranspose check box in the lower-right corner of the dialog box:

enter image description here

The article comes from Excel 2003 help, but the process applies all the way up to the newest Excel. The dialog box might look different though.

share|improve this answer
After putting each word into its own variable, how do I put the few rows into one column instead? a b c d e f g h i to a b c d e f g h i However, this is just a sample try out. I am working with large data of 2000 values So any way of going about it ? Thanks – cody_q Jun 15 '12 at 5:45
?? You can select your entire current Excel sheet, choose copy, "insert" a new worksheet, and "paste special" - transpose all of your current work at once. You don't have to do one row at a time. – Bon Gart Jun 15 '12 at 12:13

The following VBA code will do the trick.

Sub FlattenToSingleCol()
    Cells(1, 1).Activate ' Go to the top left

    ' Go past the last row that starts with a word
    Do Until IsEmpty(ActiveCell)

    ' Set curRow to the last row that starts with a word (the row above ActiveCell)
    curRow = ActiveCell.Row - 1

    Do While curRow > 0
        Cells(curRow, 1).Activate

        ' Go right past the last cell that contains a word
        Do Until IsEmpty(ActiveCell)
            ActiveCell.Offset(0, 1).Activate

        ' Come back left to the last cell that contains a word
        If ActiveCell.Column > 1 Then
            ActiveCell.Offset(0, -1).Activate
        End If

        ' Insert a new row below the current one for each
        Do Until ActiveCell.Column = 1 ' Move each non-empty cell to a new row and move left to the edge
            Cells(ActiveCell.Row + 1, 1).Value = ActiveCell.Value
            ActiveCell.Offset(0, -1).Select

        ' Go to the row above. Processing from the bottom up avoids re-processing lines we just inserted below.
        curRow = ActiveCell.Row - 1
End Sub
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .