Turning a row into a column? While importing?
 

I have a big one-line quoted list:

"value1";"value2";...;"value500"

Importing this into Excel causes it to be cut off at the 256th value as
it imports it as one row, with each value in a new column.

How can I make Excel import it with each value inserted into a new row
instead of a new column?


--
Crafty

What version of excel are you using?

This will work in xl2k and higher (Split & Replace were added in xl2k):

Option Explicit
Sub testme()

Dim myStr As String
Dim mySplit As Variant

myStr = Range("a1").Value
myStr = Replace(myStr, Chr(34), "")
mySplit = Split(myStr, ";")

Range("a2").Resize(UBound(mySplit) - LBound(mySplit) + 1).Value _
= Application.Transpose(mySplit)

End Sub

I removed the quotation marks, too. Not sure if you want that.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Crafty wrote:

I have a big one-line quoted list:

"value1";"value2";...;"value500"

Importing this into Excel causes it to be cut off at the 256th value as
it imports it as one row, with each value in a new column.

How can I make Excel import it with each value inserted into a new row
instead of a new column?

--
Crafty

--

Dave Peterson