Today's episode is a reversal of Episode #1517. In Episode #1520, Bill looks at two ways to solve the problem where you have text and a date in the same cell and the goal is to isolate only the date.
[*Note - Bill mentions Episode #1518 in this podcast, but he is actually referring to Episode #1517*]
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
[*Note - Bill mentions Episode #1518 in this podcast, but he is actually referring to Episode #1517*]
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel episode, 1520.
Separate Dates From Text.
Hey! Welcome back to the MrExcel netcast.
This is a follow-up of episode 1518, put that episode out where I said.
You know, we don't want to store the text and dates in the same cell.
I wanted to use the custom number format, a custom number format to actually store the date there.
But put the words in someone said well hey, that's great.
If you were starting from scratch, but I'm getting the worksheet like this.
It already has the text and the dates embedded.
How can I break that out?
Well, okay!
So, there's two ways to do this, kind like a dueling Excel podcast here.
The one way, is first to use some text functions to separate out that date.
So, here we can say we can just count that there are 4, 5, 6, 7 8, 9, 10 characters, before the date, and it looks like it's pretty consistent.
So, we can say equal mid of this cell comma 10 comma the most it could be is 10 characters.
That's if there were two digits for the month and year.
So, I'll ask for 10 characters which is going to get me a text representation of the date and then if I ask for the date value of that thing that looks like a date, but it's text I will get the correct answer in the wrong format, [ control 1 ] and choose a date format, click [ ok ].
All right! So, that will actually convert those texts and dates to regular old dates.
Of course, now you want to select all of those, it will do [ ctrl C ] and home, paste, paste values to convert them to regular dates and so, that's one method to go.
Although, a much faster way to go provided.
There's nothing else in this column, which in this case, there isn't, we can use the data text to columns, see how is going to do [ alt D E ], data, text to columns.
It says that it's fixed width.
Well, it don't give us a lot of sample here, but so there...
Put 20, I'm double-clicking those lines to get rid of them and putting a line right there.
All right! So, now we have filled one which is the text field to which is the date field 1.
I'm going to say do not import field 2.
I'm going to import as a date in this case, month, day, year and we can just click [ finish ] and now our text is gone and our dates, true dates remain.
Hey! I want to thank for stopping by, see you next time another netcast from MrExcel.
Learn Excel from MrExcel episode, 1520.
Separate Dates From Text.
Hey! Welcome back to the MrExcel netcast.
This is a follow-up of episode 1518, put that episode out where I said.
You know, we don't want to store the text and dates in the same cell.
I wanted to use the custom number format, a custom number format to actually store the date there.
But put the words in someone said well hey, that's great.
If you were starting from scratch, but I'm getting the worksheet like this.
It already has the text and the dates embedded.
How can I break that out?
Well, okay!
So, there's two ways to do this, kind like a dueling Excel podcast here.
The one way, is first to use some text functions to separate out that date.
So, here we can say we can just count that there are 4, 5, 6, 7 8, 9, 10 characters, before the date, and it looks like it's pretty consistent.
So, we can say equal mid of this cell comma 10 comma the most it could be is 10 characters.
That's if there were two digits for the month and year.
So, I'll ask for 10 characters which is going to get me a text representation of the date and then if I ask for the date value of that thing that looks like a date, but it's text I will get the correct answer in the wrong format, [ control 1 ] and choose a date format, click [ ok ].
All right! So, that will actually convert those texts and dates to regular old dates.
Of course, now you want to select all of those, it will do [ ctrl C ] and home, paste, paste values to convert them to regular dates and so, that's one method to go.
Although, a much faster way to go provided.
There's nothing else in this column, which in this case, there isn't, we can use the data text to columns, see how is going to do [ alt D E ], data, text to columns.
It says that it's fixed width.
Well, it don't give us a lot of sample here, but so there...
Put 20, I'm double-clicking those lines to get rid of them and putting a line right there.
All right! So, now we have filled one which is the text field to which is the date field 1.
I'm going to say do not import field 2.
I'm going to import as a date in this case, month, day, year and we can just click [ finish ] and now our text is gone and our dates, true dates remain.
Hey! I want to thank for stopping by, see you next time another netcast from MrExcel.
