In this week's dueling Excel podcast (Episode #1267), Mike and Bill take a look at formulas to perform a lookup that requires two key values to be found and displayed. Visit MrExcel.com - Your One Stop for Excel Tips and Solutions.
Transcript of the video:
Dueling Excel podcast episode, 51.
Extract Words from Dates.
Hey! Welcome back, it's another dueling excel podcast.
I'm Bill Jelen, from MrExcel.
We're joined by Mike Girvin, from Excel Is Fun.
Mike came up with a doozy, today.
Let's take a look at the problem.
Alright! It's someone sent in this data.
We have words and a date.
Sometimes a date, sometimes not a date.
They want to put the words in column B, and the date if it appears, in column C.
Alright! This is just going to be tough and I love this.
The first date is...
Takes up only 4 or 5, 6, 7, 8 characters and the last date takes up 10 characters.
This is not the best solution, but it's the one that I can come up with or without shooting over to some VBA.
So, =DATEVALUE(RIGHT That text over there, comma (,) 9 Using nine because that way even if it is 8 or 9 characters of the right hand side, I'll pick up that space and the date value continues to work.
Alright! So, that's good.
But, when I copy that down.
I see that I get some value errors here.
Well, there wasn't a date and some value errors here because the date is longer and what I expected.
So, let's do this, =IFERROR.
Alright! So, we look for that first thing, and then we will say otherwise.
I want the DATEVALUE of the RIGHT of A2, 10.
That'll get the longer values and of course we want to keep hitting right parenthesis until we get to the black one and then we know that we have a right.
Let's copy that down.
Alright! Good.
So, that gets all of my dates in we still have the value errors not you want to do for those value errors.
So, I'm going to go back and edit this formula one more time, and I'm going to use a second, IFERROR.
So, we say hey! If that whole thing evaluates to an error, then I just want quotes, quotes.
Copy that down.
Alright! Now, we're getting the dates and we're getting blanks when there is no date.
Alright! So far, so good.
You know then I thought well, hey!
This is gonna be a piece of cake.
I'll just subtract the length of what we have over here, but to my horror, =LEN date.
It is always showing up as 5.
If there's a date there, we're getting five, otherwise we're getting zero.
Okay! So.
=IF(LEN of cell C2= 0, well then there was no date.
I just want the value from over there in column A.
Alright! That works out great, right.
Otherwise then we have to do something a little bit harder and use the left of A2.
How long do we want?
Well, we want the length of A2 minus the length of well...
We can't just use C2.
I'm going to use the text of C2 and Then "M/D/YYYY".
What that'll do is, I'll take the underlying date that we calculate out there in C2.
It's going to format it.
So, it appears as, it appears over on the right hand side.
It's going to figure out the length of that whole thing.
I have the parentheses here, in the wrong spot.
So, length of that and then closed out the left function.
You see we get check, rent for month, transportation, in office party, office Eq.
You know looks like it is working Wow, like I hope you have something more elegant than that because that was ugly.
Alright! Mike let's see what you have.
Mike :Thanks, MrExcel.
Hey! I'm still going to have to go camera-less this week because my operating system still is not working with my camera.
Hey!
MrExcel. Ugly, I don't think so.
That was totally awesome!
Date value text, beautiful!
I would have never thought do that.
Now, I'm going to do something totally different, and I guess that's what's so awesome, about these duels.
Totally different approaches.
Now, if you say yours is uglier, then mine is going to be ugly too because...
And probably we should just say their elegantly ugly or something like that.
Alright! I'm going to look through this data set here and notice that for dates, there's a forward slash (/) So, I'm going to try and find the position of each one of these forward Slashes, using the SEARCH function.
Hey! The text I'm looking for "/ " , within what?
Relative cell reference one to my left.
That will just give me the position.
So, that starts with 8, 17.
There's some values there, we'll deal with that, later.
Alright! So, now that will give me the position I can use LEFT.
Now, with this range, still highlighted in the active cell right there.
I'll hit [ F2 ] and I will go LEFT of what this text right here, comma.
That so it will actually extract it, but wait a second that won't work because it found that and it found that, well!
Let's just think about this 1, 2, 3 that would give me the space, 1 2 3 that give me the K, right.
So, I'm just going to subtract 3.
Now that'll give me an extra space.
Alright! We'll deal with that in just a moment here, and I'm actually going to check this down here =LEN. That's the length.
Alright! So, it's given me 6 and it should be 5, 1, 2, 3, 4, 5.
Now, it really doesn't matter if you're just visually looking at this.
But, if you're using it as a lookup it might matter.
So, I'm going to go TRIM.
TRIM gives you a haircut or put you on a diet.
No, no ,no! It just removes the spaces except for single spaces between words.
So, that's looking good.
We can see we have our, 5.
So, the only thing left to do is, if this is an error, then please give me the whole contents of the cell.
So, I'm going to hit [ F2 ] and I'm actually going to use this SEARCH as the Trigger.
I'll use it 2003 and earlier method here.
We'll do the IFERROR, totally awesome new function.
But, I'll do how about this IF then...
Remember the search gave us a number or an error.
So, I'm going to say is ERROR.
I use this one right here, that's all errors including NAs and then I'm going to [ ctrl + v ] for that search.
So, now logical test I put a comma.
Now, if it comes out an error.
What do I want [ value if true ], I want to show.
One cell to my left, otherwise I want to show that formula.
I just had their closed parenthesis.
[ ctrl + enter ] to repopulate the column with the formula and there it is.
That looks pretty good right there.
Now, extracting the date I'm going to notice that these values are already here, and I can I count the length of it and then count in from this length, and then know that that's the position. I want to extract everything, after it.
So, I'm going to use the replace REPLACE.
The old text is right here, and what replace needs is a starting number.
I want to start at 1 , and then the number of characters I want to replace all the way to LEN.
Now, there's going to be a problem here because there'll be an extra space.
But, that should work all right.
So, from one to the length of that.
I want to comma, [ new_text ], double quote for blank, close parentheses.
[ ctrl + enter ] I can already see there's a space there.
So, I'm going to give it a haircut or a diet, TRIM.
Close parentheses and then I'm going to [ ctrl + enter ], to populate all those cells.
Now, that's a date as text and I'm simply going to hit [ F2 ] in any operation on a date as text whether it's times 1 or plus 0, will convert it back to date.
So, I'm going to + 0.
Finally, I'm going to steal MrExcel's trick here, that just beautiful new function in 2007.
It really does make it easier, and it makes a lot of spreadsheets, calculate faster because you don't have to run this twice.
We had to run the search twice over here.
So, I [ value ] [ comma ] and then just double quote for IFERROR [ ctrl + enter ] and then double click and send it down.
Alright! Throw it back to MrExcel.
MrExcel: Mike excellent. I love the [ search ].
I never use [ search ].
[ Replace ] was cool, I was about to yell at you because you were going to come up with texting dates out there instead of real dates, and you did the plus 0 but convert it all back to dates.
Gorgeous solution, great to be doing these dueling Excel podcast, again.
Well! I thank everyone for stopping by.
See you next week for another dueling Excel podcast, from Excel Is Fun and MrExcel.
Extract Words from Dates.
Hey! Welcome back, it's another dueling excel podcast.
I'm Bill Jelen, from MrExcel.
We're joined by Mike Girvin, from Excel Is Fun.
Mike came up with a doozy, today.
Let's take a look at the problem.
Alright! It's someone sent in this data.
We have words and a date.
Sometimes a date, sometimes not a date.
They want to put the words in column B, and the date if it appears, in column C.
Alright! This is just going to be tough and I love this.
The first date is...
Takes up only 4 or 5, 6, 7, 8 characters and the last date takes up 10 characters.
This is not the best solution, but it's the one that I can come up with or without shooting over to some VBA.
So, =DATEVALUE(RIGHT That text over there, comma (,) 9 Using nine because that way even if it is 8 or 9 characters of the right hand side, I'll pick up that space and the date value continues to work.
Alright! So, that's good.
But, when I copy that down.
I see that I get some value errors here.
Well, there wasn't a date and some value errors here because the date is longer and what I expected.
So, let's do this, =IFERROR.
Alright! So, we look for that first thing, and then we will say otherwise.
I want the DATEVALUE of the RIGHT of A2, 10.
That'll get the longer values and of course we want to keep hitting right parenthesis until we get to the black one and then we know that we have a right.
Let's copy that down.
Alright! Good.
So, that gets all of my dates in we still have the value errors not you want to do for those value errors.
So, I'm going to go back and edit this formula one more time, and I'm going to use a second, IFERROR.
So, we say hey! If that whole thing evaluates to an error, then I just want quotes, quotes.
Copy that down.
Alright! Now, we're getting the dates and we're getting blanks when there is no date.
Alright! So far, so good.
You know then I thought well, hey!
This is gonna be a piece of cake.
I'll just subtract the length of what we have over here, but to my horror, =LEN date.
It is always showing up as 5.
If there's a date there, we're getting five, otherwise we're getting zero.
Okay! So.
=IF(LEN of cell C2= 0, well then there was no date.
I just want the value from over there in column A.
Alright! That works out great, right.
Otherwise then we have to do something a little bit harder and use the left of A2.
How long do we want?
Well, we want the length of A2 minus the length of well...
We can't just use C2.
I'm going to use the text of C2 and Then "M/D/YYYY".
What that'll do is, I'll take the underlying date that we calculate out there in C2.
It's going to format it.
So, it appears as, it appears over on the right hand side.
It's going to figure out the length of that whole thing.
I have the parentheses here, in the wrong spot.
So, length of that and then closed out the left function.
You see we get check, rent for month, transportation, in office party, office Eq.
You know looks like it is working Wow, like I hope you have something more elegant than that because that was ugly.
Alright! Mike let's see what you have.
Mike :Thanks, MrExcel.
Hey! I'm still going to have to go camera-less this week because my operating system still is not working with my camera.
Hey!
MrExcel. Ugly, I don't think so.
That was totally awesome!
Date value text, beautiful!
I would have never thought do that.
Now, I'm going to do something totally different, and I guess that's what's so awesome, about these duels.
Totally different approaches.
Now, if you say yours is uglier, then mine is going to be ugly too because...
And probably we should just say their elegantly ugly or something like that.
Alright! I'm going to look through this data set here and notice that for dates, there's a forward slash (/) So, I'm going to try and find the position of each one of these forward Slashes, using the SEARCH function.
Hey! The text I'm looking for "/ " , within what?
Relative cell reference one to my left.
That will just give me the position.
So, that starts with 8, 17.
There's some values there, we'll deal with that, later.
Alright! So, now that will give me the position I can use LEFT.
Now, with this range, still highlighted in the active cell right there.
I'll hit [ F2 ] and I will go LEFT of what this text right here, comma.
That so it will actually extract it, but wait a second that won't work because it found that and it found that, well!
Let's just think about this 1, 2, 3 that would give me the space, 1 2 3 that give me the K, right.
So, I'm just going to subtract 3.
Now that'll give me an extra space.
Alright! We'll deal with that in just a moment here, and I'm actually going to check this down here =LEN. That's the length.
Alright! So, it's given me 6 and it should be 5, 1, 2, 3, 4, 5.
Now, it really doesn't matter if you're just visually looking at this.
But, if you're using it as a lookup it might matter.
So, I'm going to go TRIM.
TRIM gives you a haircut or put you on a diet.
No, no ,no! It just removes the spaces except for single spaces between words.
So, that's looking good.
We can see we have our, 5.
So, the only thing left to do is, if this is an error, then please give me the whole contents of the cell.
So, I'm going to hit [ F2 ] and I'm actually going to use this SEARCH as the Trigger.
I'll use it 2003 and earlier method here.
We'll do the IFERROR, totally awesome new function.
But, I'll do how about this IF then...
Remember the search gave us a number or an error.
So, I'm going to say is ERROR.
I use this one right here, that's all errors including NAs and then I'm going to [ ctrl + v ] for that search.
So, now logical test I put a comma.
Now, if it comes out an error.
What do I want [ value if true ], I want to show.
One cell to my left, otherwise I want to show that formula.
I just had their closed parenthesis.
[ ctrl + enter ] to repopulate the column with the formula and there it is.
That looks pretty good right there.
Now, extracting the date I'm going to notice that these values are already here, and I can I count the length of it and then count in from this length, and then know that that's the position. I want to extract everything, after it.
So, I'm going to use the replace REPLACE.
The old text is right here, and what replace needs is a starting number.
I want to start at 1 , and then the number of characters I want to replace all the way to LEN.
Now, there's going to be a problem here because there'll be an extra space.
But, that should work all right.
So, from one to the length of that.
I want to comma, [ new_text ], double quote for blank, close parentheses.
[ ctrl + enter ] I can already see there's a space there.
So, I'm going to give it a haircut or a diet, TRIM.
Close parentheses and then I'm going to [ ctrl + enter ], to populate all those cells.
Now, that's a date as text and I'm simply going to hit [ F2 ] in any operation on a date as text whether it's times 1 or plus 0, will convert it back to date.
So, I'm going to + 0.
Finally, I'm going to steal MrExcel's trick here, that just beautiful new function in 2007.
It really does make it easier, and it makes a lot of spreadsheets, calculate faster because you don't have to run this twice.
We had to run the search twice over here.
So, I [ value ] [ comma ] and then just double quote for IFERROR [ ctrl + enter ] and then double click and send it down.
Alright! Throw it back to MrExcel.
MrExcel: Mike excellent. I love the [ search ].
I never use [ search ].
[ Replace ] was cool, I was about to yell at you because you were going to come up with texting dates out there instead of real dates, and you did the plus 0 but convert it all back to dates.
Gorgeous solution, great to be doing these dueling Excel podcast, again.
Well! I thank everyone for stopping by.
See you next week for another dueling Excel podcast, from Excel Is Fun and MrExcel.
