George has a dataset today where a serial number contains 3 groups of digits in a single column. George needs to be able to sort by the middle group of digits. Episode 519 shows you how.
This video is the podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of 277 tips from the book!
This video is the podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today we have a question sent in by George.
If you have a question for the netcast, please feel free to either drop me an Email or leave the question on voice mail and we'll try and get you on a future podcast.
George has an interesting data set today.
He has a serial number in column A that is four digits a space another four digits a space and then it looks like seven digits at the end.
George needs to be able to sort by the middle set of digits I mean my solution to this is just going to be to insert a temporary column and extract that middle set of digits sort by that column and then delete the column.
So, to do that we want to insert a column between column A and B.
So I'll go to Column B, and use Insert, Column I'll give it a heading such as temporary and then what we need to do to grab the middle digits is a function called the MID function.
=MID(, so, we say we want the mid of whatever is in A2 and we have to figure out which position we want to start in. Well, we have four digits and a space that's five characters.
We want to start in the sixth position, and then how many characters do we want to grab, we want to grab four characters.
That's the middle four characters hit control+Enter here to take a look at that and I get 4005 which is right.
Now, to copy this down of course we'll double click the fill handle gets copied down all the way, and now you'll see that we have a new column which is just the center portion of column A we can then sort using A to Z and then very quickly we now have the data sorted of course we wanted to we could delete the extra column. Edit, Delete column and we're good to go the mid function there's also the left function if we needed to grab, the left most four characters or the right function.
If we needed to grab, the right most seven characters.
So, thanks to George for sending that question in.
Again if you have a question for the netcast, please feel free to drop me a note or leave us a voicemail and we'll get to you on a future podcast.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today we have a question sent in by George.
If you have a question for the netcast, please feel free to either drop me an Email or leave the question on voice mail and we'll try and get you on a future podcast.
George has an interesting data set today.
He has a serial number in column A that is four digits a space another four digits a space and then it looks like seven digits at the end.
George needs to be able to sort by the middle set of digits I mean my solution to this is just going to be to insert a temporary column and extract that middle set of digits sort by that column and then delete the column.
So, to do that we want to insert a column between column A and B.
So I'll go to Column B, and use Insert, Column I'll give it a heading such as temporary and then what we need to do to grab the middle digits is a function called the MID function.
=MID(, so, we say we want the mid of whatever is in A2 and we have to figure out which position we want to start in. Well, we have four digits and a space that's five characters.
We want to start in the sixth position, and then how many characters do we want to grab, we want to grab four characters.
That's the middle four characters hit control+Enter here to take a look at that and I get 4005 which is right.
Now, to copy this down of course we'll double click the fill handle gets copied down all the way, and now you'll see that we have a new column which is just the center portion of column A we can then sort using A to Z and then very quickly we now have the data sorted of course we wanted to we could delete the extra column. Edit, Delete column and we're good to go the mid function there's also the left function if we needed to grab, the left most four characters or the right function.
If we needed to grab, the right most seven characters.
So, thanks to George for sending that question in.
Again if you have a question for the netcast, please feel free to drop me a note or leave us a voicemail and we'll get to you on a future podcast.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
