Convert text to rows (Office 2000/2003)

Topic

New Reply

Is there a way to convert text to rows instead of columns? I have a file where one of the cells contains a lot of numbers, which can be easily converted to columns, so each set of numbers go in a separate cell. The problem is, however, that there are more such number sets than the number of columns available in Excel, so for me the only way to get all the data were if I could put all these in one column across several rows. I didn’t find anything useful for this, maybe I can get some help here?

Thanks!

Reply | Quote

Replies

There is no built-in “text to rows” command, so you’d have to write code to parse the text string, split it into parts and store the parts in a column. Post back if you need help, preferably with a small example of what the data look like.

Reply | Quote

Well, this is the file with the numbers in one cell. Converting to columns is not much help as it cuts off at the end of the last column. However if there is no easy solution to this, I am not sure how could I do it as my VBA knowledge is quite limited
Anyway, thanks for the comments.

Reply | Quote

Make sure that the cell with all those numbers is selected.
Press Alt+F11 to activate the Visual Basic Editor.
Select Insert | Module.
Copy / paste the following code into the module:

Sub ConvertToRows()
Dim arr
arr = Split(ActiveCell.Value, “;”)
Sheet2.Range(“A1:A” & (UBound(arr) + 1)) = arr
End Sub

With the insertion point in the code, press F5 to run the macro.
Press Alt+F11 to switch back to Excel.
Activate Sheet2 to see the result. You can copy it back to Sheet1, or wherever you need it.

Reply | Quote

Thanks a lot, I did it, however the very first number got copied over and over, it ignored the rest of the numbers. Please have a look at the attached image… I cannot reattach the file as it’s over 100k already.
Thanks again!

Reply | Quote

Sorry, I didn’t test sufficiently. Try this:

Sub ConvertToRows()
Dim arr
Dim i As Long
arr = Split(ActiveCell.Value, “;”)
For i = 1 To UBound(arr) + 1
Sheet2.Range(“A” & i) = “‘” & arr(i – 1)
Next i
End Sub

Reply | Quote

Thanks a lot, it works now. I’ve only changed to split based on spaces rather than “;” as the number right after the semicolon belongs to the first set of numbers.
Again, big thanks for such a fast and great help.

Reply | Quote

I had wondered about the role of the spaces and semicolons. I’m glad you were able to make it work the way you want.

Reply | Quote

I am good at modifying existing code but I can’t, for the life of me, write a code from scratch. Thanks a lot again, it was a life saver

Reply | Quote

Hans, if you have time…would you care to elaborate of how the code works. I had a look at the help files on the Split and Ubound functions, but its very intricate! could you share some inside info as to what these functions do and how the macro operates? It seems to be quite powerful. I would have gone miles around, searching for a space and then pasting chars all housed in a loop…..I doubt that would have worked…but this code is quick and to the point!

Many thanx!

Reply | Quote

Split(string) takes the string and parses it into individual words (delimited by a space). It creates a one-dimensional array with the words as elements.

Split(string, delimiter) works similarly, but uses the specified delimiter instead of a space (the default delimiter)

Examples:

Split(“This is Woody’s Lounge”) results in an array (“This”, “is”, “Woody’s”, “Lounge”).
Split(“123,456,789″,”,”) results in an array (“123”, “456”, “789”).

Split always returns a zero-based array, i.e. the first item has index 0, the second one has index 1, etc. The index of the first and last items of an array are returned by the LBound and UBound functions, respectively, so for the array returned by Split, LBound is always 0, and UBound is the number of elements minus 1 (since we started at 0).

The code I posted uses Split to chop the cell contents into an array of individual parts, then uses a loop to write these to cells in a column, below each other.

Reply | Quote

Wow, great explanation Hans. That is very clear now!

Reply | Quote

For those with XL97 (which does not have the SPLIT function) this function could be included to work like the VB version (albeit slower).

Function Split(sSentence As String, Optional sDel As String = " ")
    Dim sArray() As String
    Dim sTemp As String
    Dim iWords As Integer
    Dim i As Integer
    Dim iFind As Integer
    sTemp = sSentence & sDel
    
    iWords = (Len(sTemp) - Len(Application.WorksheetFunction. _
        Substitute(sTemp, sDel, ""))) / Len(sDel)
        
    ReDim sArray(0 To iWords - 1)
    For i = 0 To iWords - 1
        iFind = InStr(sTemp, sDel)
        sArray(i) = Left(sTemp, iFind - 1)
        sTemp = Mid(sTemp, iFind + Len(sDel))
    Next
    Split = sArray
End Function

Steve

Reply | Quote