Importing a Text File Into 1 Column

[Ralph]

hi,

i have a massive text file that is nothing more than a series of words
separated by commas, i.e.

dog, cat, ball, clouds, rain

there are probably 20k to 30k entries ...

is there a way i can import this file into excel so that these words are
stacked in 1 column?? i am doing it the file-import way but it pulls only so
many into 1 row, and then i have to copy-paste-transpose, etc... it will take
me 5 years to do it this way lol

PLEASE HELP!

THANKS!!

 

[Sean Timmons]

When you do the import, you should be asked to select either delimited or
fixed width.

you may want to go with fixed width, though you then risk cutting individual
words in half. May be easier than the alternative.

 

[Steve Yandl]

You could set up the macro below which would pull the strings from the text
file (In my example, the file "C:\Test\myList.txt") and place them down
Column A of the active sheet. After you copy this to a module, be sure to
edit the line
strFilePath = "C:\Test\myList.txt"
so it points to the text file you want imported.

'---------------------------------------------
Sub ImportTextAsColumn()

Const ForReading = 1
Dim strList As String

strFilePath = "C:\Test\myList.txt"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strFilePath, ForReading)

strLine = objFile.ReadAll
objFile.Close
strLine = Replace(strLine, vbCrLf, ",")

arrList = Split(strLine, ",")

For i = 0 To UBound(arrList)
Cells(i + 1, 1).Value = Trim(arrList(i))
Next i

Set objFile = Nothing
Set objFSO = Nothing
End Sub
'---------------------------------------------

Steve Yandl

 

[Gord Dibben]

Open the Text file directly with Excel.

The text to columns wizard will open.

Choose De-limited by and remove any checked options, especially comma.

Next to see what you get.

If OK........Finish.


Gord Dibben MS Excel MVP

 

[MyVeryOwnSelf]

i have a massive text file that is nothing more than a series of words

separated by commas, i.e.

dog, cat, ball, clouds, rain

there are probably 20k to 30k entries ...

is there a way i can import this file into excel so that these words
are stacked in 1 column??

One way is to first open the file in Word and use
Edit > Replace
to change all the commas by paragraph breaks: "^p" without the quotes. You
might want to remove the space characters the same way.

Then save a copy as a text file and open it in Excel.