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 receive a cell containing a list of weeks a certain class is on as follows (all in one cell)

| A [FONT=&quot]|_______________|[/FONT]
1| 20-32,35,40-41[FONT=&quot]|_______________|[/FONT]
2| 19 [FONT=&quot]|_______________|[/FONT]
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|__|__|[FONT=&quot]__[/FONT]|[FONT=&quot]__[/FONT]|[FONT=&quot]__[/FONT]|[FONT=&quot]__[/FONT]|[FONT=&quot]__[/FONT]|[FONT=&quot]__[/FONT]|[FONT=&quot]__[/FONT]|[FONT=&quot]__[/FONT]|[FONT=&quot]__[/FONT]|[FONT=&quot]__[/FONT]|[FONT=&quot]__[/FONT]|[FONT=&quot]__[/FONT]|[FONT=&quot]__[/FONT]|[FONT=&quot]__[/FONT]|
3|12|13|14|15|16|17|18|19|20|23|[FONT=&quot]__[/FONT]|[FONT=&quot]__[/FONT]|[FONT=&quot]__[/FONT]|[FONT=&quot]__[/FONT]|[FONT=&quot]__[/FONT]|[FONT=&quot]__[/FONT]|[FONT=&quot]__[/FONT]|

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.

 

[Joe4]

Duplicate: https://www.mrexcel.com/forum/excel...ecific-numbers-text-string-extract-range.html

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

Note that sometimes posts from new users require Moderator approval before you can see them on the public forums. When this happens, you should see a message to that effect when you try to post it. Please be patient and do not attempt to post the question again.