Extract specific numbers from a text string, and extract range

[danpaddy]

Hello, this is a bit of an odd query,
I am trying to convert a student timetable into an excel spreadsheet (then create a calendar with it), but the data I have been given is in an awkward format. It would be easier to give an example:

I recieve a cell containing a list of weeks a certain class is on as follows (all in one cell)

| A |[FONT=&quot] B |[/FONT]
1| 20-32,35,40-41| |
2| 19 | |
3| 12-20,23 | |

This would mean that the class runs between week 22 and 33, then again on week 35, then 40 to 41. I need to list each class seperately, so want an output to look as follows:

|A |B |C |D |E |F |G |H |I |J |K |L |M |N |O |P |Q |
1|20|21|22|23|24|25|25|26|27|28|29|30|31|32|35|40|41|
2|19| | | | | | | | | | | | | | | | |
3|12|13|14|15|16|17|18|19|20|23| | | | | | | |

Is this possible using a formula or a VBA code?

(The weeks are academic weeks and will then be converted to calender years using VLOOK up?
I know I can do it manually, but I have over 400 rows of dates to sort and could really do with a simple way to do it on mass.

Kind Regards,
Dan.

 

[MickG]

Try this:-
NBata assumed to start "A1"
NB:- This code will alter your data.

[COLOR="Navy"]Sub[/COLOR] MG18Oct21
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, S [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ray() [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    c = 0
    Sp = Split(Dn.Value, ",")
       [COLOR="Navy"]For[/COLOR] n = 0 To UBound(Sp)
           [COLOR="Navy"]If[/COLOR] Sp(n) Like "*-*" [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]For[/COLOR] S = Val(Split(Sp(n), "-")(0)) To Val(Split(Sp(n), "-")(1))
                   ReDim Preserve Ray(c)
                   Ray(c) = S
                   c = c + 1
                 [COLOR="Navy"]Next[/COLOR] S
            [COLOR="Navy"]Else[/COLOR]
                 ReDim Preserve Ray(c)
                 Ray(c) = Sp(n)
                 c = c + 1
              [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] n
Dn.Resize(, c) = Ray
Erase Ray
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[danpaddy]

Mick, you are amazing. Thank you so much. That worked instantly without any issue.

Try this:-
NBata assumed to start "A1"
NB:- This code will alter your data.

[COLOR=Navy]Sub[/COLOR] MG18Oct21
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, S [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Sp [COLOR=Navy]As[/COLOR] Variant, c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Ray() [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    c = 0
    Sp = Split(Dn.Value, ",")
       [COLOR=Navy]For[/COLOR] n = 0 To UBound(Sp)
           [COLOR=Navy]If[/COLOR] Sp(n) Like "*-*" [COLOR=Navy]Then[/COLOR]
                [COLOR=Navy]For[/COLOR] S = Val(Split(Sp(n), "-")(0)) To Val(Split(Sp(n), "-")(1))
                   ReDim Preserve Ray(c)
                   Ray(c) = S
                   c = c + 1
                 [COLOR=Navy]Next[/COLOR] S
            [COLOR=Navy]Else[/COLOR]
                 ReDim Preserve Ray(c)
                 Ray(c) = Sp(n)
                 c = c + 1
              [COLOR=Navy]End[/COLOR] If
        [COLOR=Navy]Next[/COLOR] n
Dn.Resize(, c) = Ray
Erase Ray
[COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[MickG]

You're welcome