Excel - Extracting Randomly Formatted Dates from Within Other Text - Duel 180 - Episode 276

Microsoft Excel Tutorial: Extracting randomly formatted date from a cell with other text

In this fascinating episode of Dueling Excel, Bill Jelen and Mike Girvin take on the challenge of extracting month names from a cell containing document titles with randomly formatted dates. The task at hand is to split the cell into two columns: one for the title and the other for the date. However, the dates are particularly tricky, with day numbers appearing before or after the month names.

Bill kicks off with a solution that involves building a table of month names and using the FIND function to identify which month is present in the cell. He then employs AGGREGATE to locate the earliest occurrence of a month, and further checks for the presence of a number before the month.

Mike's solution, while similar, incorporates the SEARCH function instead of FIND. His approach capitalizes on the concept of function argument array operations, which enables the evaluation of multiple answers simultaneously. To find the earliest month, he utilizes the SMALL function within AGGREGATE, a clever workaround for handling arrays within the AGGREGATE function.

The episode highlights how to troubleshoot and test formulas, showcasing the use of F9 to examine array results. Additionally, Mike's explanation sheds light on the intricacies of function argument array operations and demonstrates how to transform text numbers into actual numbers using math operations.

Ultimately, Mike's solution presents a more efficient and elegant approach to extracting the month names from these complex cells. Join Bill and Mike as they dissect the problem and unveil the inner workings of their Excel solutions.

Stay tuned for more enlightening episodes in the next Dueling Excel Podcast from MrExcel and ExcelIsFun!

Table of Contents
(00:00) Dueling Excel: Extract Month Name from a Cell
(00:32) Cell contains document title and a randomly formatted date
(01:15) Build a table of month names
(01:47) FIND returns result for all 12 months, need Mininum Non-Error
(02:07) Use AGGREGATE to ignore errors and get minimum
(02:42) Check for number BEFORE the month
(04:24) Using MID
(05:31) Mike Girvin: Similar Solution but with SEARCH instead of FIND
(06:35) Function Argument Array Operation
(07:35) Using AGGREGATE with SMALL instead of MIN
(09:46) Convert Text Numbers to Number with any Math Operation
(10:42) Finding the Number
(12:26) Using SUBSTITUTE function
(13:07) Good way to see if formula is returning an array before #VALUE!
(13:38) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #excelduel #excelformula #excelarrayformula

This video answers these common search terms:
how to use FIND or SEARCH for an array of values
Searching for multiple values with FIND or SEARCH
how to use aggregate function to ignore errors

Text in column A contains a title and a date. The date always includes a month name, but might be in different formats. Mike and Bill offer alternate strategies.
Bill's super wide approach:
Put all 12 months in separate columns
Use the FIND function to see if this month is in the original text
To find the minimum starting position, use =AGGREGATE(5,6,…
A few extra formulas to look for a number 2 or 3 positions before the month

Mike's approach:
Use SEARCH instead of FIND. Find is case-sensitive, Search is not.
Create an function argument array operation by specifying B13:B24 as Find_Text.
The formula returns #VALUE! Error, but if you press F2, F9, you will see that it is returning an array.
The first 13 functions in AGGREGATE can not handle an array, but functions 14-19 can handle an array.
5=MIN and 15=SMALL(,1) are similar, but SMALL(,1) will work with an array.
LOOKUP, SUMPRODUCT, CHISQ.TEST, INDEX, and AGGREGATE can handle function array arguments without Ctrl+Shift+Enter
Mike was smarter by looking to see if 2 characters before the Start is a number, and then grabbing 3 characters before. The extra space is eliminated by the TRIM()
To get the Title, use SUBSTITUTE function to get rid of the Date text in column C

[bricc]

How beautiful it is to watch you two brilliant minds having fun! Thank you for all that you share.

[krn]

Great to see a duel. Been a long time. Thanks guys. Was kinda expecting Bill to go VBA solution and assumed Mike would have some carefully crafted long formula. Kudos...

[MohamedAlyCLAY]

i love to see duel again :)
please don't stop it

[hermandebree]

Thanks for this. I walked in a problem using it. What to do when (sometimes) the date is at first position? "mid" and 0 won't work (Sorry for my English)

[Steve_WIEM]

These work well, until one of the cells mentions me...