Thank you!! This worked perfectly. Now I just have to try to
understand how it worked!
On Oct 29, 4:01 pm, "Akhilesh Karna" <[EMAIL PROTECTED]> wrote:
> You can run the following macro assuming that your texts are in column-A. If
> it differs, change the program accordingly. Hope it helps.
>
> Option Explicit
> '-------------------------------------
> Sub txt2arr()
> 'Assuming that all the texts are in column-A
> 'This program will fill the subsequent conlumns with the range specified
> in column-A
> 'for example if cell-A1 =>6, 8-10
> ' B1=6,C1=8,D1= 9, E1 = 10 and so on
> 'to functions 'splitComma' and 'splitDash' will be used
> Dim ar() As String
> Dim ar2
> Dim i As Byte
> Dim j As Byte, k As Byte
> Dim n As Integer
> n = 1
> Do While (Range("A" & n) <> "")
> ar = splitComma(Range("A" & n))
> j = 2
> For i = LBound(ar) To UBound(ar)
> ar2 = splitDash(ar(i))
> For k = LBound(ar2) To UBound(ar2)
> Cells(n, j) = ar2(k)
> j = j + 1
> Next k
> Next i
> n = n + 1
> Loop
> End Sub
> '-------------------------------------
> Function splitComma(t As String)
> splitComma = Split(t, ",")
> End Function
> '-------------------------------------
> Function splitDash(t As String)
> Dim dSplit
> Dim i1 As Integer, i2 As Integer
> Dim j As Integer
> dSplit = Split(t, "-")
> i1 = Val(dSplit(0))
> If UBound(dSplit) = 0 Then
> splitDash = dSplit
> Exit Function
> End If
> i2 = Val(dSplit(1))
> ReDim splitVal(i2 - i1) As Integer
> For j = 0 To i2 - i1
> splitVal(j) = i1 + j
> Next j
> splitDash = splitVal
> End Function
> '------------------------------------------------END
>
> Akhilesh
>
>
>
>
>
> On Wed, Oct 29, 2008 at 1:12 PM, <[EMAIL PROTECTED]> wrote:
>
> > Hi, I need help with a spreadsheet. The spreadsheet is created using a
> > program I have no control over. I have a column that can contain weeks
> > that a course is run. It seems to come out in the following formats:
>
> > Weeks
> > 10 - 12
> > 8
> > 6, 11 - 12
> > 4 - 6, 8 - 10
> > 3 - 7, 11
>
> > I want to split each and have a comprehensive list of the weeks. I
> > want it to look like this:
>
> > 10 11 12
> > 8
> > 6 11 12
> > 4 5 6 8 9 10
> > 3 4 5 6 7 11
>
> > But I have thousands of rows and do not want to do it manually. I have
> > tried using text to columns in many different ways and was able to
> > get:
>
> > 10 12
> > 8
> > 6 11 12
> > 4 6 8 10
> > 3 7 11
>
> > But this does not help. You don't know now if the first one was 10 and
> > 12 or 10 through 12. Can anyone help? I have a nifty macro to count up
> > the final number of weeks and insert that many rows underneath to have
> > a line for each week the course but I just can't work out an automatic
> > way to get all the course weeks in separate cells.
>
> --
> Akhilesh Kumar Karna- Hide quoted text -
>
> - Show quoted text -
--~--~---------~--~----~------------~-------~--~----~
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/excel-macros?hl=en
Visit & Join Our Orkut Community at
http://www.orkut.com/Community.aspx?cmm=22913620
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com
To see the Daily Excel Tips, Go to:
http://exceldailytip.blogspot.com
-~----------~----~----~----~------~----~------~--~---
