In a recent post I audited my colleague’s amazing array formula. It extracted the largest number from as420lkjs09nsdk2324lkjsd099 See other solutions suggested by YouTube viewers!

 

Flash Fill?

 

Ankur Shukla’s comment reminded me that the fastest way to solve this challenge is to use FlashFill! If the pattern is consistent let FlashFill solve it! Type in the pattern (usually 1 or 2 entries is enough), click ‘Data’ and ‘Flash Fill’. Done!

 

Array Formula?

The array solution is slow to calculate on a large data-set but it’s beautiful! And sometimes we need a dynamic formula (Power Query requires a refresh, Flash Fill needs rerunning). Arrays should be used sparingly like a fine wine but they can calculate the impossible and I love them! (see end of post for alternative array)

 

Power Query?

Kunle SOPEJU sent me his Excel Power Query solution. It’s amazing! WHY ? (see end of post for alternative PQ solution)

If we had thousands of rows of data the array would be heavy. Why use so much calculation effort on extracting the largest number in each cell when Power Query can do the heavy lifting and drop the answers back into the sheet! Also, if we learn Power Query we can automate other tedious tasks!

 

Download the Excel file and follow my detailed explanation of Kunle’s Power Query solution.

 

Power Query Solution

I can confidently use basic features in Power Query but I’m not an advanced user (yet). To learn more I’ll audit Kunle’s solution!

Input & Output

 

 

 

 

We start in worksheet “sheet1” (seen above).

The blue table is the original data. The green table is what Power Query exports back to the sheet.

We see the largest number results in column D! Let’s audit the steps inside Power Query.

 

Opening Power Query

Let’s enter the magical world where all the action takes place!

1. Select any cell in the green table
2. On the ribbon select ‘Query Tools’ and ‘Edit’

 

First Glance

The original untouched data is below on the left (column ‘AlphaNum’).

PROPERTIES shows the query name: “Stepwise – Largest Number in String“

APPLIED STEPS lists each step. Oyekunle has clearly labeled the steps for us 🙂

Next is to explore the details of each applied step.

 

 

 

 

 

 

 

 

 

 

STEP 1  “Source”

= Excel.CurrentWorkbook(){[Name=”Table6″]}[Content]

Pic above shows us that the data is loaded from “Table6” (before any steps take place).

 

STEP 2  “AddCol – Txt2List”

Click Applied Step AddCol – Txt2List to see this:

 

 

 

 

 

We can see each step’s full code in the formula bar but it’s a bit confusing.

There’s 2 ways to dig into the details to help understand what’s going on:

(a) click the circular gear icon to the right of this step’s name

 

 

Now you should see this “Custom Column” box. This step uses this text function:

=Text.ToList( [AphaNum] )

 

 

 

 

 

Tip: click here to see Microsoft’s definition & example of Text.ToList

 

 

(b) in column “Txt2List” click the white space next to “List” in any row.

You’ll see each individual character split out into a vertical list like this:

 

 

 

 

 

 

 

 

 

STEP 3  “AddCol – Replace Txt with Spaces”

Click applied step “AddCol – Replace Txt with Spaces”. Click the gear to see the function:

List.Transform( [Txt2List] , each if Value.FromText( _ ) is text then ” ” else _ )

It looks at each value in the list. If it’s a text then it becomes a space.

In column “Transform List” click any white space beside “List”. Letters are removed!

 

 

 

 

 

 

 

 

 

STEP 4  “Add Col – CombineText”

Click applied step “Add Col – CombineText”

Clicking the gear reveals: Text.Combine( [TransformList] )

Spaces and numbers in the vertical list are flipped back into a horizontal cell.

 

 

 

 

 

 

STEP 5  “Add Col – SplitText by Spaces”

Click applied step “Add Col – SplitText by Spaces”

Clicking the gear reveals: Text.Split( [CombnTxt] , ” ” )

Numbers separated by spaces in each cell are now split apart in this new list. COOL !

In column “Split Text” click any white space beside “List” to reveal:

 

 

 

 

 

 

 

 

The list contains all the individual numbers! (but they are stored as text)

 

**We are almost finished now!**

 

STEP 6  “Add Col – Transform Txt to Numbers”

Click the gear to see function: List.Transform( [SplitTxt] , each Value.FromText( _ ) * 1 )

Each text number is multiplied by 1 to convert into a real number.

 

STEP 7 “Add Col – Obtain Largest Number in List”

Function List.Max ( [#”ListTransform – Txt2Number”] ) grabs the largest number in the list!

 

STEP 8 “Removed Other Columns”

Now we have our final answer so we can hide all the intermediary steps.

 

Send Query Answers to Sheet1

In the top left of the ‘Home’ tab Kunle clicked “Close & Apply” drop down (NOT the icon above) and then “Close & Load to…” to select exactly which sheet and cell to put the answers!

 

 

About Kunle (Oyekunle)  SOPEJU

Currently, Oyekunle offers his services as a Resource person to Training and Consulting outfits in Sales and Data Analysis. He is a Faculty Member/Consultant with AutusBridge Consulting Ltd (Training & Consulting) in Lagos, Nigeria. He is a Certified Tutor of both Institute of Sales Management, United Kingdom. (ISM-UK) and Cambrigde Professional Academy, UK.

He is particularly interested in bridging the Data Science Gap. His favorite data analysis tools and apps is MS Excel, Power Query with M-Language, Power Pivot with DAX and of course Power BI.

Oyekunle has worked in Pharmaceuticals, FMCG  and Telecommunications both in Nigeria and Ghana.

 

About Ankur Shukla

Ankur is an accountant and Excel guru from Lucknow, Uttar Pradesh, India. You can find him on Linkedin and ExcelForum.com

Thanks Ankur for all the comments & suggestions you’ve made on my YouTube channel!

 

Power Query BONUS from Kunle

Thank you Kunle for your solution! Auditing it has helped my understand Power Query Lists!

What’s the BONUS?

Inside Power Query click ‘Queries’ (left side). We see Oyekunle’s “Stepwise – Largest Number in String” query that we just audited.

Audit other queries & custom functions like “fnLargestNumberInAString” to learn more!

 

 

 

 

 

 

 

Pro Method

Instead of writing each step individually you could open the ‘Advanced Editor’ and write the M code.

This requires a lot of practice but some can do it! Here is Oyekunle’s M code:

let
Source = Excel.CurrentWorkbook(){[Name=”Table6″]}[Content],
#”AddCol – Txt2List” = Table.AddColumn(Source, “Txt2List”, each Text.ToList( [AlphaNum] )),
#”Add Col – Replace Txt with Spaces” = Table.AddColumn(#”AddCol – Txt2List”, “TransformList”, each List.Transform( [Txt2List] , each if Value.FromText( _ ) is text then ” ” else _ )),
#”Add Col – CombineText” = Table.AddColumn(#”Add Col – Replace Txt with Spaces”, “CombnTxt”, each Text.Combine( [TransformList] )),
#”Add Col – SplitText by Spaces” = Table.AddColumn(#”Add Col – CombineText”, “SplitTxt”, each Text.Split( [CombnTxt] , ” ” )),
#”Add Col – Transform Txt to Numbers” = Table.AddColumn(#”Add Col – SplitText by Spaces”, “ListTransform – Txt2Number”, each List.Transform( [SplitTxt] , each Value.FromText( _ ) * 1 )),
#”Add Col – Obtain Largest Number in List” = Table.AddColumn(#”Add Col – Transform Txt to Numbers”, “LargestNumber in String”, each List.Max ( [#”ListTransform – Txt2Number”] )),
#”Removed Other Columns” = Table.SelectColumns(#”Add Col – Obtain Largest Number in List”,{“AlphaNum”, “LargestNumber in String”})
in
#”Removed Other Columns”

Select ‘Home’ at the top and then ‘Advanced Editor’ to see this code.

It takes practice to understand M code. Auditing the steps carefully makes it easier. Remember that:

• a step starts with “#”
• each step refers to the previous step
• values were juggled back & forth between cells and lists
• various functions were used
• Oyekunle clearly renamed each step (easier to audit !)

 

Learn With Me!

 

 

 

 

 

 

 

 

I’ve been taking this amazing course taught by Ken Puls and Miguel Escobar. I’ve learned so much!

Disclaimer: i’m a student and an affiliate.

 

Alternative Array Solution

YouTuber ‘GaribaldiInTheMaking‘ suggested this alternative array formula that uses the versatile AGGREGATE function. It’s a longer formula that might be faster!

=AGGREGATE(14,6,0+TRIM(MID(TEXTJOIN(“”,FALSE,IF(ISNUMBER(0+MID(A2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2))),1)),MID(A2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2))),1),REPT(” “,LEN(A2)))),LEN(A2)*(ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2)))-1)+1,LEN(A2))),1)

 

Alternative Power Query Solution

Daniel Choi (his Excel blog) shared three Power Query solutions with me! Thanks Daniel! Unfortunately it doesn’t work on my version of Excel 2016 but feel free to download it.

 

Solution Update

In the comments below Bill Szysz suggested a one step Power Query solution:

let
Source = Table.AddColumn(Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name=”Table6″]}[Content],{{“AlphaNum”, type text}}), “Largest”, each List.Max(List.Transform(Text.Split(Text.Combine(List.Transform(Text.ToList([AlphaNum]), each try Text.From(Number.From(_)) otherwise ” “)), ” “), each try Number.From(_) otherwise null) ) )
in
Source

I think both the step by step method by Kunle and Bill’s one step method are amazing! Sometimes we need to break things down into steps to see exactly how they work. And then it’s also great to know how to create a short compact solution.

 

About Me

My name is Kevin Lehrbass. I live in Markham Ontario Canada.

These are my dogs Cali and Fenton. They sit with me when I write my blog posts. They know when I need a break from my laptop 🙂

I’ve been a Data Analyst since 2001. My favorite software is Microsoft Excel. I’m currently learning Power BI.