How do I paste text into Excel and get each individual word into its own cell?

Question

I have large amount of text that I want to copy into Excel or Word so I can alphabetize them by individual word. So, for example, I would end up with a list like: a a a a an an and and action activate actually, etc...

Answer 1

I have done this before and could not find a simple way to do it.

You can sort in Word so no need to import the text into Excel.

First you need to convert your body of text into one word per line. Use "Find and Replace" (press CTRL+H) and replace all spaces with a paragraph break.

Remove any punctuation the same way - eg search for full stops and replace with nothing.

You will probably end up with some extra blank lines in the result. You could try to remove all these by searching and replacing incidences of 2 paragraph marks with only 1. However, if you simply select all the text and then use Table > Sort to sort all the lines into alphabetical order, all the blank lines will end up together anyway and you can delete them then.

If you wanted to import the text into Excel to end up with one word per cell, simply select all the text (CTRL+A) in your one-word-per-line Word document, copy it (CTRL+C), open Excel or switch to it and paste (CTRL+V) the text into your spreadsheet.

There is also a concordance feature in Word which would enable you to index your original text document (if you keep a copy) by using the sorted word order document (edited so each word only occurs once) as the source file enabling Word to generate a concordance which will tell you on which page each word in the source file occurs.

HTH

Answer 2

Excel can put them in order but u would have to paste each word in its own cell. Once each word is in, on one of the menu bars you should see and down arrow with about 3 letter going down, push it and excel has put them in order, make sure you highlighted everything.

Answer 3

This macro will do what you are asking for. You have to paste your text into a text file, then run this macro from within excel.



Sub Macro1()

'

' Macro1 Macro

' Macro recorded 11/20/2006 by MD

'



'Copy your text to a text file named "c:\exceltest.txt" to test.

'If you want to use a differant file name, edit the Open statement.



Dim tIN As String

Dim iLen As Integer

Dim tChr As String * 1

Dim iWordCnt As Integer



iWordCnt = 1



Open "c:\exceltest.txt" For Input As #1



Do While Not EOF(1)

Line Input #1, tIN

iLen = Len(tIN)



For i = 1 To iLen

tChr = Mid(tIN, i, 1)

Select Case tChr

Case " " 'End of Word

GoSub WriteWord

Case ".", "?", "!", Chr(13) 'End of Sentance or Line

If i = iLen Then

GoSub WriteWord

End If

Case ",", "(", ")" 'Skip These

Case Else

tWord = tWord & tChr

End Select

Next i

Loop

Close 1



Exit Sub



WriteWord:

Sheet1.Cells(iWordCnt, 1) = tWord

tWord = ""

iWordCnt = iWordCnt + 1

Return



End Sub

Answer 4

high the text u want then right click and choose paste, to make the cell bigger drag the corner of the box.

Answer 5

little confused by what you mean, you should be able to paste it in the cells, the tick on the above column you may find useful.



for some reason im thinking of merging cells.!