## Summary

In this challenge, 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". What formula will translate the "N" and "Y" to weekday abbreviations?

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?

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

### Assumptions

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

## 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.