Excel - Extracting Randomly Formatted Dates from Within Other Text - Duel 180 - Episode 276
2,080 views · Published 14 April 2017 · 14:11 · Indexed 16 November 2026
Channel: MrExcel.com · 2017 · Education
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 Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153904/
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920