Converting Comma and Dash-Separated Number Strings to Binary

Good morning my fine Excel friends. Excel Washington here with a burning desire for a solution to a problem that's probably simple but I can't see it.

At its most basic, I'm looking to convert text/number string into a binary-style format. I work with timetable data, and ideally I receive timetable data from the clients with which I work, with the binary week of each booking (one row represents one timetable booking) already included within the spreadsheet. Clients to like to send over some crazy data exports, however, so lots of the time I end up with weeks represented as numbers or strings of text/numbers.

The attached spreadsheet contains a rough template of the data format I work with.

The blue column in the first tab represents the kind of number strings I'm looking to convert, and the Peach column is the outcome I'm looking to achieve. 52 weeks in the year, 0s representing the non-booked weeks, 1s representing the weeks I'm looking to extract from the number string.

It gets more complex considering there are some weeks separated by dashes, which require that all weeks in-between and inclusive of those numbers are also represented as 1s in the binary format (16-20 would be weeks 16,17,18,19,and 20 being 1's for example). I have no clue how to achieve this

The other tab contains the very crude attempt I made at a solution, which once included IfIsnumbers and searches, but to no avail so I gave up on this.

Is there anyone here that could potentially provide a formula-based solution that could be copied/pasted easily and used for future versions of this very same problem?

My gratitude would be eternal

Thank you Phuocam, this solution works for me, only with one issue.

The code doesn't seem to recognize number strings that consist solely of two numbers with a dash between (eg. 14-18), however is fine with all other combinations.

I've attached a picture to demonstrate the issue.

If this is fixed then this will be a complete solution

Many thanks

Hi Olly,

Thanks for your reply. I'll also give that a go. I'll need to read your Power Query guide first though.

Many thanks

I have updated the file in post # 2

Or try:

Please Login or Register  to view this content.