Extract specific numbers from a text string, and extract range

danpaddy

New Member
Joined
Oct 18, 2018
Messages
3
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.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Try this:-
NB:biggrin:ata assumed to start "A1"
NB:- This code will alter your data.
Code:
[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
 
Upvote 0
Mick, you are amazing. Thank you so much. That worked instantly without any issue.

Try this:-
NB:biggrin:ata assumed to start "A1"
NB:- This code will alter your data.
Code:
[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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top