A work colleague shared this challenge: extract the largest number from this text as420lkjs09nsdk2324lkjsd099 ! The answer is 2324. How do we solve this? Formulas? VBA?
Before solving this we need to know the full context. Is it 200 cells? 900000 cells?
We have 1000 alphanumeric cells. With this small amount we can consider almost any approach.
My colleague solved this challenge with this amazing array formula:
(after pasting it into formula bar you need to hold Ctrl & Shift keys and then press Enter)
At first I didn’t understand it. But once I audited it I just loved it! I’ll show you why!
Let’s backup and review how we normally use the MID function. =MID(F4,5,3)
In cell F4, go to 5th position, extract 3 characters. We get 0lk
Three parts: cell reference, starting point, number of characters to extract.
But we’re going to extract multiple values from each point inside cell F4 !
All the big action happens in this step!
Guts of the Formula
Peek Inside The Array!
- in cell G4 click inside the formula bar
- highlight MID(F4,ROW($A$1:$A$30),COLUMN($A$1:$Y$1))
- press F9 key (on laptop hold Fn key and press F9 key)
This is a sample of what you’ll see!
We only want pure numbers like “4”, “42”, “420” etc.
(press ‘Esc’ key to go back to the formula)
Multiply each string by 1. Text numbers convert into a number. Everything else is an error.
Adding IFERROR changes all errors to blank. We are left with just the numbers.
MAX function gives us the largest number 2324 !
Change the numbers inside cell F4 or F5 to make sure the solution works.
2 Dimensional Array Explained
Q: What’s the difference between the commas and semi-colons inside the array?
A: The semi-colons are part of the vertical array created by the ROW function. The commas are part of the horizontal array created by the COLUMN function.
Each one is one dimensional but when used together a cool two dimensional array is created! 🙂
Via a google search I found vba code that solved this but the code made my head spin. I love vba but it was intense. In this case my colleague’s array formula wins!
Power Query solution?
I’m curious if anyone can solve this using Power Query (Get & Transform). Please let me know 🙂
Update: comments here & below my video provide Power Query solutions! Thanks to Daniel Choi and Oyekunle SOPEJU !
Thanks to Ankur shukla for suggesting the Flashfill solution! If the pattern is consistent flashfill’s internal logic can fill the results almost instantly after we show it the pattern.
My name is Kevin Lehrbass. I’m a Data Analyst living in Markham Ontario Canada.
I’ve been working with data since 2001 but I’m still learning so much!
I wouldn’t want to ever stop learning. I’m lucky that my job is also my hobby!
Check out my recommended training section.