Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jul 2004
    Location
    Chicago, Illinois, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    One Cell Into Many Rows (Excel 2000)

    Cell A1 contains 13,242 whole numbers, each separated by a semi-colon. I need to separate all these numbers into individual rows. I tried columns to rows but Excel will not accommodate 13,242 columns. I tried putting it into Word and still couldn't do it. I need to perform this function every month with different numbers (in one cell) and a different amount of numbers. Thanks in advance for your help.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: One Cell Into Many Rows (Excel 2000)

    Try his macro.

    <code>
    Public Sub SplitNums()
    Dim strWk As String
    Dim I As Long
    strWk = Trim(Range("A1").Value)
    If Len(strWk) = 0 Then Exit Sub
    If Right(strWk, 1) <> "," Then
    strWk = strWk & ","
    End If
    I = 0
    Do While Len(strWk) > 0
    Range("A1").Offset(I, 0).Value = Trim(Left(strWk, InStr(strWk, ",") - 1))
    I = I + 1
    strWk = Trim(Right(strWk, Len(strWk) - InStr(strWk, ",")))
    Loop
    End Sub
    </code>
    Legare Coleman

Posting Permissions

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