Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 .

Example:

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:

Catherine
is
a
good
girl
.
share|improve this question
add comment

2 Answers 2

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
add comment

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)
        DoEvents
        ActiveCell.Offset(1).Select
    Loop

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

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

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

        ' 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
            DoEvents
            ActiveCell.Offset(1).EntireRow.Insert
            Cells(ActiveCell.Row + 1, 1).Value = ActiveCell.Value
            ActiveCell.Clear
            ActiveCell.Offset(0, -1).Select
        Loop

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.