Regex is incredibly powerful and versatile. I’ll share some resources, a challenge and several techniques that are helping me learn.

What is Regex?

Regex (aka Regular Expressions) finds, extracts, and validates text using patterns instead of exact words. It evolved from a mathematical concept into a practical tool for text processing. 

Why learn Regex?

Q: Why should we spend time learning regex syntax?
A: Even basic regex knowledge can save hours of work.

Regex in Microsoft Excel 365

Regex was introduced in Excel in 2024. Three functions use regex syntax:

• REGEXEXTRACT
• REGEXREPLACE
• REGEXTEST

Regex Intro Resources

Start with regex theory from Mynda Treacy, Leila Gharani, Dave Burns, and Sumit Bansal:

• MyOnlineTrainingHub: excel-regex-functions
• MyOnlineTrainingHub: regex-regular-expressions-excel
• MyOnlineTrainingHub: Excel Regex Cheatsheet
• Xelplus: Regex in Excel
• Xelplus: Introducing REGEX Excel functions
• Exceljet: REGEXEXTRACT function
• TrumpExcel: New Excel REGEX Functions are INSANELY Powerful

Don’t memorize too much theory. Solve problems as soon as possible!

Like learning a language: speak ASAP! Learn basic grammar, speak, repeat. Some regex theory, practice, more theory, more practice. Let’s review a challenge!

Regex LinkedIn Challenge

Challenge Definition

From Mohammad Abou Daher: split column A text into three columns: ID, Capital, State Code.

Complex Solution

Mohammad’s solution is brilliant but the average Excel user wouldn’t understand this:

=REDUCE({"ID","Capital","State Code"}, A3:A12,
     LAMBDA(x,y,
           VSTACK(x,
                 LET(
                    a, TEXTJOIN("",, IFERROR(MID(y, SEQUENCE(LEN(y)),1)*1,"")),
                    b, CONCAT(REGEXEXTRACT(y,"[A-Z]{2}")),
                    c, SUBSTITUTE(SUBSTITUTE(y,a,""),b,""),
                    HSTACK(a,c,b)
                 )
           )
     )
 )

LAMBDA function is fascinating but complex. Let’s solve it an easier way that most users will understand.

Less Complex Solutions

First, I’ll solve it using three separate formulas.
Then, I’ll combine all three formulas into a single formula.

Three Separate Formulas

Formula 1: cell D3

=REGEXEXTRACT(A3:A12,”^[0-9]+”)

• text: range A3:A12 causes formula to spill results to row 12 (or use A3 and drag formula down)
• pattern: “^[0-9]+”
  • ^ the pattern must start at the beginning of a cell
  • [0-9] digits from zero to nine
  • + take all continuous digits from the start of the pattern
• [return_mode] and [case_sensitivity] are optional and not needed

Formula 2: cell F3

=REGEXEXTRACT(A3:A12,”[A-Z]{2}”)

• text: range A3:A12 causes formula to spill results to row 12 (or use A3 and drag formula down)
• pattern: “[A-Z]{2}”
  • [A-Z] upper case text from A to Z
  • {2} take ONLY the first two letters
• [return_mode] and [case_sensitivity] are optional and not needed

Formula 3: cell E3

=TEXTAFTER(A3:A12,D3#&F3#)

• text: range A3:A12 causes cell E3 formula to spill results to row 12
• delimiter: D3#&F3# (cell E3: take whatever comes after “1042TX” in cell A3)
  • concatenate cell D3 value with cell F3 value
  • # symbol: concatenate spill values below cells D3 and F3
• other function arguments are optional and not needed

These formulas solve the challenge and are easier to understand and explain to others. Why is this important? Because spreadsheets are passed around to different departments and companies.

Single Formula

=LET(
Titles,     {"ID","Capitol","State Code"},
Text,       A3:A12,
ID,         REGEXEXTRACT(Text,"^[0-9]+"),
State_Code, REGEXEXTRACT(Text,"[A-Z]{2}"),
Capital,    TEXTAFTER(Text, ID&State_Code),
Stack,      HSTACK(ID, Capital, State_Code),
Final,      VSTACK(Titles, Stack),
Final)

It looks complex doesn’t it? I’ll explain it from the beginning.

Single Formula Step 1

Cell I3 LET function allows us to define all 3 formulas inside 1 formula.
# spill character isn’t used. HSTACK horizontally stacks them.

Single Formula Step 2

In step 1, I repeat range A3:A12 in each formula. Now, variable Text is used in each formula. I can reuse the formula and change the range once.

Single Formula Step 3

Lastly, I created variable Titles for the column headers. Variable Final uses VSTACK to place column headers on top of the columns.

Now that we’ve solved a regex problem, what’s next? Let’s use AI to study regex examples and then experiment with sample text!

Tools to Continue Learning

Modern tools make it SO much easier to ramp up our regex skills!

Use AI to create regex examples

I used Perplexity AI on my phone with this prompt:

Create a list of 1000 regex sample solutions using Microsoft Excel regex functions

Perplexity created groups. Below, the first 15 examples in group ‘Basic pattern matching’

1. Basic pattern matching (1–60)

1. =REGEXEXTRACT(A2,"[0-9]+") (extract first number sequence)​
2. =REGEXEXTRACT(A2,"[A-Za-z]+") (extract first contiguous letters)​
3. =REGEXEXTRACT(A2,"^[0-9]+") (extract leading digits)
4. =REGEXEXTRACT(A2,"[0-9]+$") (extract trailing digits)
5. =REGEXEXTRACT(A2,"^\s*[A-Za-z]+") (extract first word, ignoring leading spaces)
6. =REGEXEXTRACT(A2,"\b\w+\b") (extract first word using word boundary)
7. =REGEXEXTRACT(A2,"\b\d{4}\b") (extract 4‑digit number, e.g., year)
8. =REGEXEXTRACT(A2,"\b[A-Fa-f0-9]{6}\b") (extract 6‑digit hex)
9. =REGEXEXTRACT(A2,"[A-Z]{3}") (extract first 3 capital letters)
10. =REGEXEXTRACT(A2,"[A-Za-z]{2,}") (extract first word with 2+ letters)
11. =REGEXEXTRACT(A2,"[aeiouAEIOU]") (extract first vowel)
12. =REGEXEXTRACT(A2,"[^0-9]+") (extract first non‑numeric run)
13. =REGEXEXTRACT(A2,"[^A-Za-z]+") (extract first non‑letter run)
14. =REGEXEXTRACT(A2,".{5}") (extract first 5 characters)
15. =REGEXEXTRACT(A2,"^.{3}") (extract first 3 characters)

I study the examples and occasionally lookup syntax definition. It’s great!

regexr.com

I’m using regexr.com to practice. I love it!
There’s a sample expression, text and explanation. ([A-Z])/w+ highlights words with an upper case letter. Top left area has helpful features to explore.

Modify the expression and observe the results. Here are some I made:

• ([e-f])]w+
• ([c-h)\W+
• (^[E-Z])\w+
• ([a-z^])\w+

Ensure ‘Explain’ is selected below. The explanations are really helpful!

Next, I logged in via google account and started exploring with a new set of sample text.
a[^aeiou] highlights a lower case a followed by anything except lower case aeiou.

In Excel, I used the expression inside function REGEXEXTRACT:
Cell B6 formula counts the occurrences of any lower case letters followed by anything except aeiou.

regex101.com

regex101.com is a similar site where you can practice regex expressions. \b\w{4}b highlights words with a length of 4 characters.

In Excel, this formula extracts all four letter words:

=REGEXEXTRACT(A2,”\b\w{4}\b”,1)

I kept experimenting late last night past 1am!
I’ll now learn more theory in a different area like numbers, email, url, dates etc. and use these tools to practice with a new set of sample data!

Conclusion

Knowing even a few basic regex patterns can save hours of work!
Complex expressions require work to build but remember how we solved things before regex! Slowly and painfully using basic string manipulation functions.

About Me

Me and a very friendly cat named Ox. My cousin loves animals and volunteers at a cat shelter. I wish I could have adopted Ox but my dogs Cali and Fenton would have protested!

I love learning patterns in Excel, Chess, Spanish grammar, Kakuro etc. It’s fun. I know it’s an over used phrase but I really am I life long learner. I’m not happy if I’m not learning something.

When modeling in Excel should we only use traditional functions or combine traditional and dynamic array functions? Let’s use segmentation as an example.

What is segmenting data?

Data segmentation: dividing data into meaningful subgroups.
Below, two columns of data from Hablando de Excel.
Age: range B7:B106
Value: range C7:C106

Requirement: Sum values per age group (group increment is ten).

Now we know WHAT segmentation is, but HOW should we calculate it?

Traditional or Dynamic Array Functions?

Traditional Functions: multiple formula solution using traditional basic functions (IF, MIN, MAX, SUMIFS). Each cell produces an independent result. Some formulas are manually dragged down.

Dynamic Array Functions: single formula spills results to multiple cells by combining traditional functions (IF, MIN, MAX, SUMIFS) and dynamic array functions (LET, GROUPBY, VSTACK, HSTACK).

Hablando de Excel combines 2 traditional functions (SUM, ROUNDDOWN) with 2 dynamic array functions (LET, GROUPBY).

(English available in settings)

Let’s compare solutions to understand pros and cons.

Excel file: Albert’s original file with my formulas added.

Traditional Functions: solutions 1 and 2 both use 17 formulas that must be manually dragged down if the number of segments increases. Increment amount can be changed without touching the formulas. Boundaries are visible to decrease formula complexity.

Dynamic Array Functions: solutions 3 and 4 use only 1 formula! You’ll NEVER need to drag the formula down further as it automatically spills down/across as needed. MAGIC! But, it’s complex and difficult to modify for most people.

Let’s compare solution 3 and 4 in more detail:

Solution 3 is from Hablando de Excel. Thank you Albert! His formula:

=LET(vi, ROUNDDOWN(B8:B107,-1), GROUPBY(vi&"-"&vi+9,C8:C107,SUM,0,0))

It only uses two dynamic arrays: LET and GROUPBY. It could be explained to people with intermediate knowledge. In many cases it’s the perfect formula.

Solution 4 is mine. Why is my formula so complex???

=LET(
age,   B8:B107,                  values, C8:C107,           size,10,
low,   MIN(FLOOR(age,size)),     high, MAX(FLOOR(age,size)),
rows,  (high-low)/size+1,
group, SEQUENCE(rows,1,low,size),
calc,  SUMIFS(values,age,">="&group,age,"<"&group+size),
title, HSTACK("Age","Value"),    data, HSTACK(group&"-"&group+size,calc),
final, VSTACK(title,data),
final)

Empty bins:
I found something that might mean nothing to some and everything to others.

• all solutions have bins starting with the lowest rounded down value (21 rounded to 20)
• each upper bin is 10 above
• if we change the data (check box in cell U2) solution 3 skips bin 30-39 as there’s no data (this may or may not matter to you)

It may be obvious why a bin is missing. Imagine outliers well above the rest of the data. Why include many bins with no data?
In other cases, it would be important to include empty bins and investigate why they are empty.

Increment amount:
Solution 3 and 4 include the increment amount inside the formula. This could easily be changed to include it in a cell like in solutions 1 and 2.

Can you think of a shorter solution that includes empty bins?
Or, a formula that includes a switch to include/hide empty bins?

If segmenting is new to you, scroll down to the Segmenting Guidelines section.

Let’s not forget my original question: When modeling in Excel should we only use traditional functions or combine traditional and dynamic array functions?

Below, formulas are highlighted in grey. Solutions 1 & 2 use 17 simple formulas but it’s a lot to audit and maintain.
Imagine a workbook with many complex sheets. I’d rather audit one complex formula that uses dynamic array functions than dozens of formulas that contribute to the answer and require modifications.

Not convinced? Read this post that reviews Carl Seidman’s course: Advanced Excel for Financial Planning and Analyst (FP&A).

Segmenting Guidelines

M.E.C.E. explanation from Google AI search:

The M.E.C.E. (Mutually Exclusive, Collectively Exhaustive) principle is a data segmentation framework that ensures categories for a dataset or problem-solving structure are distinct and complete. 

The two core rules for M.E.C.E. segmentation are:

• Mutually Exclusive (ME): Each data record or item fits into one and only one category. There is no overlap among groups, which prevents double-counting and ambiguity.
• Collectively Exhaustive (CE): All possible items, scenarios, or options are covered by the established categories. The sum of all categories equals the whole, ensuring no relevant information is overlooked. 

Application in Data Segmentation

When segmenting data using the M.E.C.E. principle, the goal is to create a structure that is clear, comprehensive, and efficient for analysis and decision-making. 

• Ensures clarity: Each item has a single, specific place, which avoids confusion and makes analysis straightforward.
• Guarantees comprehensiveness: The process forces you to consider all possibilities, reducing the risk of missing important factors or segments.
• Improves efficiency: By avoiding overlap, you prevent the duplication of work when different teams or processes are assigned to different segments. 

Examples of M.E.C.E. Segmentation

Financial Metrics: Breaking down profit into “Revenue” and “Costs” is M.E.C.E. because all components of profit are included, and no value can be both a revenue and a cost simultaneously.

Age Brackets: Segmenting a population by age groups like “0-17”, “18-65”, and “66+” is M.E.C.E. because everyone falls into one category, and no one falls into two simultaneously.

Geographic Regions: Dividing sales data by continents (e.g., North America, South America, Europe, Asia, etc.) ensures that all global sales are covered and none are double-counted.

What’s in a Name?

A summary from ChatGPT to compare segmenting terms.

🧮 Statistical & Analytical Terms

There’s no single universal term that covers grouping, segmenting, and binning perfectly across all contexts, but here’s how professionals typically refer to this concept depending on how broad you want to be:

✅ Most Common General Term

“Data segmentation”

This is the most broadly understood and widely used umbrella term for dividing data into meaningful groups — whether that’s by bins, categories, or clusters.
It’s common in analytics, business intelligence, statistics, and machine learning.

“The model uses segmented input features to capture nonlinearity.”

Used when: You’re dividing a dataset into parts for analysis, modeling, or interpretation.

Examples: “We performed data segmentation based on customer age and spending.”

About Albert Cid Cañigueral

Albert is a Microsoft Excel MVP from La Sénia, Catalonia, Spain.
He has a treasure chest of over 800 YouTube videos!

Follow Albert here:
Linkedin linkedin.com/in/hablandodeexcel
Facebook facebook.com/AlbertCidC
Instagram instagram.com/albert_cidc
tik tok tiktok.com/@albert.cid.hd.excel

About Me

My name is Kevin Lehrbass. I’m a Data Analyst and I live in Milton, Ontario, Canada. Fenton and Cali are my amazing dogs! My other hobbies include playing chess and learning Spanish.
That’s why I enjoyed Albert’s video so much. I just watched his most recent video: RETO EXCEL: ¿Eres capaz de NORMALIZAR DATOS CON FÓRMULAS? Another great video! I can understand very well but I need to learn Excel function names in Spanish. I can guess some of them, but not all of them.

I’ve seen some amazing videos showcasing the potential of dynamic array functions but seeing them used in a model is even more impressive!

We all know how easily things can get out of control when writing a complex formula. It may provide the correct answer, but will anyone understand it?

A good definition from Copilot:

The clarity, simplicity, and interpretability of a formula, enabling users to quickly understand its purpose, logic, and components without needing extensive explanation.

Requirements

Given the data on the left, create the output on the right:

• use a single formula
• all parts horizontally aligned (column M) even if text length changes
• include title “NHL 1980-1981 Season Round 1 Playoff Bracket”
• copy/paste single formula to reuse solution

Yes, it would be MUCH easier to use five columns and align text as needed…but I wouldn’t get to use the LET function!

Solution 1

=VSTACK("NHL 1980-1981 Season Round 1 Playoff Bracket",
        LET(
        t, B3:B18,  s,B3:B10,
        M,MAX(LEN(s))+1,  N,MAX(LEN(t)),
        arrayI,{1;2;3;4;5;6;7;8},b,17-arrayI,
        arrayI&" "&INDEX(s&REPT(" ",M-LEN(s))&"vs ",arrayI)&
        INDEX(t&REPT(" ",N-LEN(t)),17-arrayI)&" "&IF(LEN(b)=1," "&b,b)))

At first I liked this formula due to it’s short length. When I went back later to change something, I realized it was difficult to audit.

Solution 2

LET function is amazing! It lets us reduce function names to a single letter:

=VSTACK("NHL 1980-1981 Season Round 1 Playoff Bracket",
LET(
t, B3:B18, s,B3:B10, X,MAX, L,LEN, I,INDEX, R,REPT,
m,X(L(s))+1, n,X(L(t)),
a,{1;2;3;4;5;6;7;8},b,17-a,
a&" "&I(s&R(" ",m-L(s))&"vs ",a)&
I(t&R(" ",n-L(t)),17-a)&" "&IF(L(b)=1," "&b,b)))

Shorter but even more difficult to audit! I like the idea of being kind to my future self when auditing/modifying a formula weeks or months in the future.

Solution 3

I finally settled on this formula. Much longer but kind 🙂

=VSTACK("NHL 1980-1981 Season Round 1 Playoff Bracket",
LET(
TopTxt,B3:B10, EndTxt,B11:B18, RankA,{1;2;3;4;5;6;7;8}, RankB,17-RankA, RankC,RankB-8,

a,N("RankBB pushes rank 9 one additional space to the right"),
RankBB,IF(LEN(RankB)=1," "&RankB,RankB),

b,N("Calc longest text for top & bottom team sets. Pad others with spaces"),
LongTxtA,MAX(LEN(TopTxt)),
LongTxtB,MAX(LEN(EndTxt)),
PadA,REPT(" ",LongTxtA-LEN(TopTxt)),
PadB,REPT(" ",LongTxtB-LEN(EndTxt)+1),

c,N("Concatenate parts together"),
RankA&" "& INDEX(TopTxt&PadA,RankA)
&" vs " & INDEX(EndTxt&PadB,RankC)&RankBB))

Ideas I used in this formula:

• longer descriptive variable names for ranges and calculations
• ALT + ENTER for carriage returns (imagine without them!)
• N function to comment a section of similar items (except 1st row of LET which I think is easy to read)
• final formula: concatenates parts together (no additional calcs)
• could I have used a single range along with TAKE function?

Update: use “comment1” etc as variable names (no need for N function). Thanks to Mahmoud Bani Asadi!

=VSTACK("NHL 1980-1981 Season Round 1 Playoff Bracket",
LET(
TopTxt,B3:B10, EndTxt,B11:B18, RankA,{1;2;3;4;5;6;7;8}, RankB,17-RankA, RankC,RankB-8,

comment1,"RankBB pushes rank 9 one additional space to the right",
RankBB,IF(LEN(RankB)=1," "&RankB,RankB),

comment2,"Calc longest text for top & bottom team sets. Pad others with spaces",
LongTxtA,MAX(LEN(TopTxt)),
LongTxtB,MAX(LEN(EndTxt)),
PadA,REPT(" ",LongTxtA-LEN(TopTxt)),
PadB,REPT(" ",LongTxtB-LEN(EndTxt)+1),

comment3,"Concatenate parts together",
RankA&" "& INDEX(TopTxt&PadA,RankA)
&" vs " & INDEX(EndTxt&PadB,RankC)&RankBB))

And this is why we share ideas! We all learn and we all win!

Recap

We all have different skills and preferences…but I don’t think anyone enjoys auditing mega formulas! Even if you don’t like your colleagues, you should be kind to your future self!

LET function’s ability to define reusable variables reminds me of DAX variables that breaks logic into smaller pieces.

• How would you have solved this using a single formula? (leave a comment below)
• What have I not mentioned that’s essential to my solution? (non formula related)

Have you ever?

Have you ever used CELL function’s width info_type? Check out sheet ‘easy solution’!

Hmm…I noticed that manually checking a column’s width is not always the same result as CELL function. AI gave me what seems to be a solid answer:

1980-1981 NHL Season and Playoffs

The NHL created playoff pairing based on overall team rank (not division or conference) for the 81 playoffs. It was fair, but my team got clobbered in the first round by the reigning and eventual repeat champions (1980s New York Islanders dynasty). Cub fans and Red Sox fans endured longer before finally winning again….one day it will happen.

About Me

My name is Kevin Lehrbass. I’m a data analyst. My other hobbies include: learning Spanish, playing chess, and drawing.

I used this post by Meganathan Elumalai to explore the transition from traditional formulas to spill formulas (formula results spill into multiple cells).

Meganathan’s requirements are clear:

Meganathan used a table in column B for the source items. In this post I’ll use a non table range (my Excel file includes both).

Formula1:

Cell D5 formula, in the picture above, is also listed below:

=IF(ISERR(SEARCH(D$4,$B5)),"",D$4)

Will it spill? No. This formula won’t spill into other cells.

Why doesn’t it spill?

SEARCH function’s default use is to look for a single cell value within another single cell like we have here: SEARCH(D$4,$B5)

Cell D5 Formula Description

Look for cell D4 search word (Apple) within cell B5. If doing this causes an error (Apple isn’t within cell B5), then display a blank (“”). If it is found then display cell D4 search word (Apple).

This is the traditional method to solve this. The formula is then dragged down and right as needed. There’s nothing wrong with this but now we have more options: a formula can spill answers into other cells without being dragged or copied!

Before looking at Formula2, I’ll explain two things in my Excel file:

Conditional Formatting

Cells with formulas are highlighted in green (see picture below) if the check box is checked:

• Formula1 in cell D5 was dragged down to row 10 and across to column I so all cells are green
• Formula2 in cell D17 automatically spills down to row 22! I dragged cell D17 formula to the right to cell I17. Each formula spills down to row 22 (without being dragged down!)

Search Words (fruit)

• In range D4 to I4, search words (fruit) are typed in
• In cell D16, an array constant formula spills fruit names to the right until cell I16. The only formula in range D16 to I16 is in cell D16

Formula2:

Cell D17 formula, in the picture above, is also listed below:

=IF(ISERR(SEARCH(D$16,$B$5:$B$10)),"",D$16)

Will it spill? Yes. Row 17 formulas spill down, not right (cell D17 formula is dragged right to cell I17).

How does it spill down?

As we saw earlier, SEARCH is typically used to look for a single cell value inside of another single cell (exception: array formulas) like this: SEARCH(D$4,$B5)

But, Formula2 SEARCH(D$16,$B$5:$B$10) searches for Apple in a range of cells: $B$5 to $B$10 creating multiple answers. Cell D17 formula spills down to cell B22 as range $B$5:$B$10 has 6 cells.

• D17 formula: cell D16 search word Apple is in cell B5 so display Apple
• D17 formula spills to cell D18: cell D16 search word Apple isn’t in cell B6 so blank
• D17 formula spills to cell D19: cell D16 search word Apple is in cell B7 so display Apple
• D17 formula spills to cell D20: cell D16 search word Apple isn’t in cell B8 so blank
• D17 formula spills to cell D21: cell D16 search word Apple is in cell B9 so display Apple
• D17 formula spills to cell D22: cell D16 search word Apple isn’t in cell B10 so blank

Cell D17 formula is then dragged right until cell I17.

Remember: only cells with green background have a formula. Other cells receive spill formula results or have typed in text values.

Formula3:

The formula in cell D29 is listed below:

=IF(ISERR(SEARCH(D28#,$B$5:$B$10)),"",D28#)

Will it spill? Yes! Cell D29 formula spills down AND to the right!

How does it spill down AND right?

Three factors allow cell D29 formula to spill down AND right:

1. cell D28 has an array constant formula that spills search words right to cell I28
2. cell D29 has # after both D28 cell references (D28#) telling it to spill right as wide as the spill range of the array constant above
3. cell D29 SEARCH function has a multi cell within_text argument range ($B$5:$B$10) causing it to spill down to row 34

Let’s validate this by changing cell D29 formula.

Remove the ability to spill right by changing D29 formula to this:

=IF(ISERR(SEARCH(D28,$B$5:$B$10)),"",D28)

Remove the ability to spill down by changing D29 formula to this:

=IF(ISERR(SEARCH(D28#,$B5)),"",D28#)

Formula4:

The formula in cell N4 is listed below:

=LET(
header,{"Apple","Banana","Grape","Kiwi","Orange","Pear"},
formula,IF(ISERR(SEARCH(header,$B$5:$B$10)),"",header),
VSTACK(header,formula))

Will it spill? Yes! Cell N4 formula spills search words and then spills answers down AND to the right.

How does it spill search words AND formula results?

• LET function lets us define a name for a calculation (or array constant)
• the name header has this formula: {“Apple”,”Banana”,”Grape”,”Kiwi”,”Orange”,”Pear”}
• the name formula has this formula: IF(ISERR(SEARCH(header,$B$5:$B$10)),””,header)
• VSTACK function vertically stacks these two spill pieces together

Formula5:

The formula in cell N16 is listed below:

=LET(
header,Fruit,
formula,IF(ISERR(SEARCH(header,$B$5:$B$10)),"",header),
VSTACK(header,formula))

Will it spill? Yes! Cell N16 formula spills just like Formula4 except that name header refers to a named range called Fruit that has an array constant.

Formula6:

The formula in cell N29 is listed below:

=DROP(REDUCE(0,B5:B10,LAMBDA(a,i,VSTACK(a,MAP(D4:I4,LAMBDA(x,IF(ISNUMBER(FIND(x,i)),x,"")))))),1)

Will it spill?

Yes…but I can’t explain this formula yet. It was created by Excel MVP Rick Rothstein. It combines a lot of dynamic array functions so I definitely want to study it!

About Me

My name is Kevin Lehrbass. I’m a data analyst.

Formulas that spill results into other cells are very powerful and have changed the way we build Excel models. They require special attention as they can also cause issues if not designed and tested carefully.

Blendtec company founder Tom Dickson experiments to see what his blender can chop up. Microsoft Excel formulas are like that too…we have to study and experiment to see what they can do!

I enjoy Kakuro puzzles. They are challenging and rewarding. In this post I’ll explain: