Exceljet

Quick, clean, and to the point

Formula challenge - convert Y/N to days of week

A long-time reader sent me an interesting problem this past week. The goal is to end up with a text string like "MWF" for Monday, Wednesday, Friday. The problem is that the weekdays are input as yes/no abbreviations like "NYNYNYN" for "MWF".

Challenge

What formula will translate the "N" and "Y" to weekday abbreviations as shown in the screenshot above?

The workbook is attached below. Post your answer in the comments.

Extra points for style and elegance, but workhorse solutions are fine, too :)

Assumptions

All inputs are 7 characters and contain only "Y" or "N"
Days are mapped Sunday through Saturday, SMTWTFS.

Attachments

convert y n to days of week challenge.xlsx

Answer (click to expand)

Solution options - spoilers!

Option #1 - brute force concatenation with the MID function, line breaks added for readability:

=IF(MID(B5,1,1)="Y","S","")&
IF(MID(B5,2,1)="Y","M","")&
IF(MID(B5,3,1)="Y","T","")&
IF(MID(B5,4,1)="Y","W","")&
IF(MID(B5,5,1)="Y","T","")&
IF(MID(B5,6,1)="Y","F","")&
IF(MID(B5,7,1)="Y","S","")

This would be a typical solution, and nicely illustrates how concatenation works. Note: you are free to use line breaks inside the formula bar to make formulas easier to read.

Option #2 - TEXTJOIN and MID function:

=TEXTJOIN("",TRUE,IF(MID(B5,{1,2,3,4,5,6,7},1)="N","",{"S","M","T","W","T","F","S"}))

This solution uses array constants to simply the formula considerably.

Note: Jon Wittwer posted a more sophisticated version of this formula in the comments below, spinning up the array constant using ROW and INDIRECT.

Option #3 - TEXTJOIN, MID, and REPT :

=TEXTJOIN("",1,REPT({"S","M","T","W","T","F","S"},MID(B5,{1,2,3,4,5,6,7},1)="Y"))

A *slightly* more compact version using REPT, taking advantage of the fact that MID will return TRUE or FALSE for each value, and TRUE will evaluate to 1 or zero inside REPT.

More Formula Challenges

form.antibot { display: none !important; } You must have JavaScript enabled to use this form.

I have relied on your website on countless occasions throughout the course of my graduate level finance internship. Compared to a number of unnamed Excel tutorial sites and forums, yours is par-none. It is invariably the only source for consistently accurate and understandable formula functions and syntaxes. I say that as this page has solved an hours-long problem facing me during an all-night work session. Thanks a lot! -Nick

Formula challenge - convert Y/N to days of week

Challenge

Assumptions

Solution options - spoilers!

Download 200+ Excel Shortcuts

Excel video training