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
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: 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 : 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. Author
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
