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
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
