Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Convert text to rows (Office 2000/2003)

    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!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Convert text to rows (Office 2000/2003)

    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.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert text to rows (Office 2000/2003)

    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 [img]/forums/images/smilies/smile.gif[/img]
    Anyway, thanks for the comments.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Convert text to rows (Office 2000/2003)

    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.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert text to rows (Office 2000/2003)

    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!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Convert text to rows (Office 2000/2003)

    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

  7. #7
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert text to rows (Office 2000/2003)

    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.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Convert text to rows (Office 2000/2003)

    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.

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Convert text to rows (Office 2000/2003)

    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!
    Regards,
    Rudi

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Convert text to rows (Office 2000/2003)

    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.

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Convert text to rows (Office 2000/2003)

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

    <pre>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</pre>


    Steve

  12. #12
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert text to rows (Office 2000/2003)

    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 [img]/forums/images/smilies/smile.gif[/img]

  13. #13
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Convert text to rows (Office 2000/2003)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •