Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing csv file all data is in first column | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
How to group similar column titles together???? | Excel Discussion (Misc queries) | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
Formula for a column | Excel Discussion (Misc queries) |