Formula challenge - convert Y/N to days of week

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.