MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Finding Dates


April 14, 2017 - by Bill Jelen

Finding Dates

Some of the questions that come in are pretty difficult. Today, we have a column of cells. Each cell has some words, then a date, then some more words. The goal is to pull the date portion of that text to a new column. This is a dueling episode with ideas from Bill and Mike.


Watch Video

  • 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

Auto-Generated Transcript

  • hey welcome back it's time for another
  • dueling Excel podcast I'm Bill Jelen
  • from mr. X I'll be joined by Mike Gervin
  • from Excel is fun this is our dual
  • number 170 finding dates hey welcome
  • back everyone I had such a great
  • question here and I couldn't solve it at
  • least I couldn't solve it easily so I
  • went out to my car and I said Mike hey
  • do you have a way to do this he said yes
  • I have a way to do it let's do a duel so
  • someone on YouTube send in this data and
  • every single cell in general has
  • something like a document title followed
  • by a date they wanted to break this data
  • into the document cite what it is what
  • the thing is and then what the date is
  • but the dates are completely evil like
  • here it's 20 of January but down here
  • there's things where the date might be
  • after the cell April 9th all right and
  • no matter which way it is we want to
  • find it and sometimes there's two dates
  • and this is just completely horrible in
  • that is such a just a mixed up situation
  • of dates in as possible do not even have
  • a date show up all right so here's my
  • attempt out on the right hand side I'm
  • gonna put the things I'm looking for
  • what I really like here is they never
  • abbreviated the month name I really
  • really appreciate that so type in
  • January and I'll drag out here to
  • December like that and for each cell I
  • want to know can we find equal find that
  • chain here so I'm going to press that
  • for one two times to lock it down to
  • just the row in the text over there and
  • column a like that I'll press f4 one two
  • three times to lock it down to the
  • column right and here it's telling us
  • the January is found in position 32 and
  • for the other 11 months it's going to
  • tell us that it's not found at all in
  • other words we're getting the value
  • error now what I need to do there is I
  • need to find I need to find the minimum
  • value ignoring all of the value errors
  • so unhide this little formula here equal
  • aggregate
  • and let's build this just from scratch
  • equal acronym what we want is the min so
  • that's the number 5 and then ignore the
  • error values number 6 comma and then all
  • of these cells from January through
  • December and what that's going to tell
  • us is that's going to tell us where the
  • month happens and in this case we're
  • gonna get 0 so the month doesn't happen
  • at all all right now let's have the hi
  • the rest of this so to handle the
  • situation we're here we have 20 January
  • or 1 November I said the first thing I'm
  • gonna do is I'm gonna look at where that
  • month starts and go back to cells two
  • cells two characters two characters and
  • see if that is a number not so that's my
  • column here called just to adjust to and
  • here's what we're gonna do I'm gonna say
  • take the mid of a two starting it though
  • we're in G 2 minus 2 for a length of 1
  • add 0 to it and ask is that a number or
  • not all right so is that a number and
  • then we'll also look for the situation
  • where it is a two-digit date so 20th of
  • January so that is called adjust 3 go
  • back 3 characters from the where so
  • there's the word go back three
  • characters for length of one as you were
  • to it and see if that's a number all
  • right then we're gonna adjust and the
  • adjusted where says if how if it's this
  • weird case with 0 we're just going to
  • put a really large value 9 and otherwise
  • we're going to go from G 2 and either go
  • back 3 if it just three is true too or
  • go back to adjust to is true or if none
  • of those are true the wear is going to
  • be where the month starts right now that
  • we know that that adjusted where we'll
  • double click to copy that down well hey
  • now it's really easy we're just gonna
  • for the title
  • we're gonna say take the left of a - how
  • many characters you want we want D 2
  • minus 1 because that's the minus 1 is to
  • get rid of the the space at the end
  • although I guess the trim is also
  • getting rid of the space at the end and
  • then for the date we're gonna use the
  • mid mid we're gonna mid of a 2 starting
  • at the adjusted we're in d2 and go out
  • 50 or whatever along you think it could
  • possibly be and then the trim function
  • and we will double click to copy that
  • down all right now the reason I reached
  • out to Mike is I said I wonder if
  • there's a way that I could replace these
  • 12 columns with a single form actually
  • these 13 columns with a single form is
  • there some way that I could do this
  • using an array formula and Mike of
  • course wrote that great book control
  • shift enter on array formulas and I
  • tried a few different things and in my
  • mind there was no way that it could be
  • done all right
  • but you know let's go ask the expert so
  • Mike let's see what you have thanks mr.
  • Excel hey and speaking of expert this
  • was pretty expertly done you used to
  • find aggregate is number men now when
  • you sent this question over to me I went
  • ahead and solved it and it is amazing
  • how similar my solution is to yours all
  • right I'm gonna go over to this sheet
  • here I'm gonna start with figuring out
  • where the start position in this text
  • ring is for each particular month now
  • the way I'm going to do it is I'm gonna
  • hey use this search function now you use
  • to find I use search actually probably
  • find is better in this situation because
  • find is case-sensitive search is not now
  • normally what we do with either finder
  • search I say hey go find January comma
  • within this larger text ring that's how
  • we normally use surf's control enter and
  • it counts on its finger one two people
  • and says the 32nd character is worth
  • found January now instead of doing it in
  • many cells across the columns I'm going
  • to hit F to come up here and the fine
  • text notice we gave it one item search
  • gave us one answer but if I highlight
  • the
  • higher column of month names now instead
  • of a single item I put many items in
  • there this is a function argument we're
  • putting an array of items in and so that
  • means we're doing a function argument
  • array operation any time you do that you
  • tell the function hey give me 12 answers
  • one for each month now this will deliver
  • an array so if I try to enter this and
  • copy down it's not gonna work well let's
  • go down to any particular cell f2 and
  • then f9 to look that yes in fact it is
  • delivering on an array and look at that
  • it looks like I do up here forgot to
  • lock it so I'm gonna click on that and
  • f4 control enter double click and send
  • it down F to F 9 there we go that's that
  • array there's exactly 12 answers and
  • there's the 34 and the 55 now from this
  • array since we always want the actual
  • first month not the second month we want
  • whatever the min is because those are
  • number of characters in from the left so
  • I'm going to click escape come up to the
  • top f2 I'm gonna use the aggregate
  • function hey we would like to use
  • aggregate 5 but no matter how hard you
  • try if you have an array operation and
  • we do here if you try to put any
  • function 1 to 13 it just doesn't work
  • but no problem we have small so number
  • 15 comma any one of those functions 14
  • to 19 they understand array operations
  • and once you select 14 or above this is
  • the screen tip you're working off of not
  • this bottom one with the references
  • alright comma the second options here we
  • want to ignore errors comma that number
  • 6 will then tell aggregate to look
  • through here and ignore the errors it
  • will only see the numbers and this is
  • one of five functions in Excel look up
  • some product chai square test aggregate
  • and index that actually have a special
  • argument that can handle array
  • operations without doing any
  • special Keystone so there is the array
  • comma and then for K we simply put a 1
  • that's our way of getting them in close
  • parentheses control enter double click
  • and send it down and so that tells us
  • the position where it found the first
  • month name from this list now we'll deal
  • with the number at the very end in our
  • final formula now we are gonna have to
  • take these and notice that sometimes
  • there's a number before the month and
  • sometimes like down here in December
  • there is not so I'm gonna do the same
  • thing MrExcel dude I'm gonna go back
  • to characters and check whether it is a
  • letter or in this case on number equals
  • mid there's the text comma the starting
  • position well I want to start at 32 in
  • this case and subtract 2 to go back to
  • and comma I get exactly one character
  • now if I close parenthesis mid left
  • right they all deliver text double click
  • and send it down and we want to check if
  • it's a number so watch this the whole
  • column is highlighted active cell at the
  • top I'm going to hit f2 we can do any
  • math operation to convert text numbers
  • back to number so I'm going to add 0
  • control enter to populate this edited
  • formula down through the column control
  • enter now we can ask the question is the
  • returned item a number f2 so now I say
  • is number close parentheses control
  • enter so now we have a pattern of trues
  • and falses now remember we need to get
  • the starting position and for 32 we're
  • definitely gonna have to subtract 3 and
  • start at that 20 but notice down here we
  • don't want to subtract any so our
  • logical test if I hit f2 that will
  • simply be put into the if logical test
  • argument if that comes out true comma
  • then I want to jump back 3 comma
  • otherwise I want to jump back 0 close
  • parentheses control enter to populate
  • that all the way down now we can take
  • this number and subtract the number over
  • here to give us our starting position
  • active cell at the top f2 I'm putting
  • this inside of me
  • there's the text comma and can you
  • believe it all of this to get the start
  • number so I'm going to click on that be
  • two and subtract our F come to the end
  • comma and I'm just gonna put a big
  • number in here one hundred some big
  • number big enough to get all the way to
  • the end close parenthesis and control
  • enter to populate that all the way down
  • it looks like we have some extra spaces
  • and that makes sense because right here
  • we went back three so no problem active
  • cell at the top f2 I'm going to use the
  • haircut function the diet function no
  • the trim function to remove extra spaces
  • except for single spaces between words
  • come to the end close parentheses
  • control enter to populate that all the
  • way down now I have the date except for
  • the num now I could come to the top and
  • use if error but then it would run all
  • of this plus that cell right there and
  • for a small data set it doesn't matter
  • at all but with the goal of efficiency
  • I'm going to say if is number and I'm
  • gonna click on that cell that way close
  • parentheses comma the trigger for
  • whether we run the formula is only based
  • on that instead of the entire formula if
  • that comes out true we want to run the
  • formula comma otherwise double quote
  • double quote that zero link text ring
  • will show nothing control enter
  • double click and send it down and now
  • all we need to do is get the title well
  • I already have the text that I don't
  • want in here so I'm gonna use the
  • substitute function substitute there's
  • the text comma the old text it's that
  • right there comma the new text hey I
  • want to take that and substitute in
  • nothing there's our zero link text ring
  • control enter or double click and send
  • it down now I'm going to come over here
  • to column B right click hide and there
  • we go
  • alright throw it back to MrExcel hey
  • Mike that is brilliant and I know
  • exactly exactly where
  • I went wrong right here in row 12 when
  • the formula returned the pound value
  • error you press f2 f9 to see that it's
  • returning an array I when I got the
  • value error I just swore a little bit
  • and said why isn't this working never
  • thought of pressing f2 f9 all right now
  • so like that of course min and small
  • comma 1 are the same but the difference
  • is small comma 1 will work with an array
  • in the aggregate function that was a
  • beautiful beautiful trick and then I
  • went through that whole hassle to look
  • at 2 characters before and 3 characters
  • before you were smart enough to say hey
  • we're gonna go 2 characters before and
  • if so go back 3 characters worst case
  • you get a space for that extra space and
  • eliminated by the trim and then the
  • cherry on top using substitute function
  • to get rid of the date text in column C
  • what a brilliant brilliant way to go
  • alright so I want to thank everyone for
  • stopping by we'll see you next time for
  • another dueling excel podcast from mr.
  • excel and excel is fun

Download File

Download the sample file here: Duel180.xlsm

Title Photo: dimitrisvetsikas1969 / Pixabay


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.