importing delimited data into single column

[inspired]

Is there a way in Excel 2007 to import spacedelimited data into a
single column?

The format of the input file is:

word1 word2 word3 word 4
word5 word6 word7 word8
.. . .

When I do a text import, it goes into multiple columns.

Thanks in advance

 

[Dave Peterson]

Make sure the name of the file has an extension of .txt.

Then use Ctrl-0 (equivalent of file|open) to open the text file.

You'll be prompted with a wizard that will allow you to import the text file
anyway you want.

 

[inspired]

Dave,
Thanks for our suggestion, but unfortunately, this does not
work. It gives me the same wizard as when I use the the text import
in Excel 2007.

If there is a way to use this wizard to get the data in a single
column, please let me know. I cannot figure out any way to do this.

Alan

 

[inspired]

If you are seeing the Wizard, in the 2nd? step where you have an
opportunity to select "delimiter", merely ensure that all the boxes
are DEselected.

This does not work, either. Since the input file format is:

<line 1> word1 word2 word3 word 4
<line 2> word5 word6 word7 word8
. . .

then following your suggestion results in:

<Cell A1> word1 word2 word3 word 4
<Cell A2> word5 word6 word7 word8
. . .

If I use a space delimiter, then I get:

<Cell A1> word1 <Cell B1> word2 <Cell C1> word3 <Cell
D1> word4
<Cell A2> word5 <Cell B2> word6 <Cell C2> word7 <Cell
D2> word8

Instead, what I desire is something like: (order not important)

<Cell A1> word1
<Cell A2> word5
<Cell A3> word2
<Cell A4> word3
<Cell A5> word4
<Cell A6> word6
<Cell A7> word7
<Cell A8> word8

Thanks, Alan

 

[Gord Dibben]

Go ahead and use space-delimited to get the words into 4 columns.

Then run this macro to move all to Column A as you wish.

Sub rowstocolumn()
'bob phillips....March 15th, 2010
'must be no blanks in the matrix
With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For i = LastRow To 1 Step -1

For J = LastCol To 2 Step -1

.Rows(i + 1).Insert
.Cells(i, J).Cut .Cells(i + 1, "A")
Next J
Next i
End With
End Sub


Gord Dibben MS Excel MVP

 

[Dave Peterson]

How did you use the wizard?

Details matter.

Dave,
Thanks for our suggestion, but unfortunately, this does not
work. It gives me the same wizard as when I use the the text import
in Excel 2007.

If there is a way to use this wizard to get the data in a single
column, please let me know. I cannot figure out any way to do this.

Alan

 

[Dave Peterson]

Ahh. I see what you wanted. Each word in its own cell all in column A.

 

[inspired]

Dave,
I chose the following settings in the Wizard:

Original Data Type: Fixed width
Start import at row: 1
File origin: 437: OEM United States
Data preview: default (no changes)
Column data format (for each column): Text

thanks

 

[Dave Peterson]

My suggestion will put the whole line into a single cell in a single row (just A1).

That's not what you want.

Check some of the other responses that included the VBA code.

 

[inspired]

Thank you all for the feedback. I was originally wondering whether or
not there was a clean way to do this on import. Since there is not, I
went the VBA route.