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