Michael has data in column A. He needs to extract everything after the dash to column B. This podcast shows four different methods. If you ever wondered how to create fake data for your own Excel podcast, watch the outtake at the end.
For information on the May 9 Tampa Power Excel seminar, visit: ow.ly/w9xMG
For information on the May 9 Tampa Power Excel seminar, visit: ow.ly/w9xMG
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Check out the MrExcel Podcast live when I do Power Excel at Tampa, Florida on Friday, May 9, 2014.
Learning Excel from MrExcel Podcast Episode 1880: Extract Suffix to New Column.
Hey, welcome back to the MrExcel netcast, I’m Bill Jelen.
Today’s question is sent in by Michael.
He has a column of values and he needs to get the Suffix, everything after the – over to a new column.
And the tricky part is the amount of characters before the – and after the – might be different in every case, so I need to use the MID function, we’re going to ask for the =MID(A2,FIND(“-“,A2)+1 so that will locate where the – is.
And then start 1 more than that.
And at this point, I really have a couple of choices.
I could ask for the LEN(A2)-FIND+1 or we could just ask for some insanely large number like 10 and know that that will get all the characters.
The big question is if we ask for more characters than what are there, what are they going to do with it?
Are they going to pat with spaces or are they going to just give us the characters over there?
Great news, they just give us the characters that are there.
So, that’s one way to go.
The beautiful thing is if we discover that there’s an error and we correct it, it will get corrected in the formula.
If you know that this data is not going to change it all, then a much faster way to go is with Data Text to Column so Alt+DE.
I’m going to say the data is Delimited and step 2, I’m going to say it’s Delimited by the – and very carefully, in step 3, I’m going to say, “Hey, we do not want to import the first column, we only want to import the second column and the destination is not going to be Column A, it’s going to be Column B.” Click Finish and we adjust the suffix.
Now, if something changes here, it won’t change there.
The same thing with this one, this is an Excel 2013 trick using Flash Fill, it creates a… They could see the Flash Fill is there but then they decided against it.
So, we’re going to come back here to data and Flash Fill.
Also, make sure that they get the B19 correct.
It’s strange that they could sense there was an answer there but then they must not have been sure enough, so they backed out.
Again, this is only in Excel 2013.
And in the example that Michael sent, his values before the – were always exactly the same.
If that happens to be the case, I wasn’t sure from Michael’s data if I was just seeing a small portion of it or not.
We can actually just count here that we want to start in =MID(A2,8,10) and that will reliably work if you know the values before the – are always the same.
Well, hey, I want to thank Michael for sending that question in.
I actually met Michael at one of my live Power Excel seminar so check out the one in Tampa.
I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel.
Check out the MrExcel Podcast live when I do Power Excel at Tampa, Florida on Friday, May 9, 2014.
Learning Excel from MrExcel Podcast Episode 1880: Extract Suffix to New Column.
Hey, welcome back to the MrExcel netcast, I’m Bill Jelen.
Today’s question is sent in by Michael.
He has a column of values and he needs to get the Suffix, everything after the – over to a new column.
And the tricky part is the amount of characters before the – and after the – might be different in every case, so I need to use the MID function, we’re going to ask for the =MID(A2,FIND(“-“,A2)+1 so that will locate where the – is.
And then start 1 more than that.
And at this point, I really have a couple of choices.
I could ask for the LEN(A2)-FIND+1 or we could just ask for some insanely large number like 10 and know that that will get all the characters.
The big question is if we ask for more characters than what are there, what are they going to do with it?
Are they going to pat with spaces or are they going to just give us the characters over there?
Great news, they just give us the characters that are there.
So, that’s one way to go.
The beautiful thing is if we discover that there’s an error and we correct it, it will get corrected in the formula.
If you know that this data is not going to change it all, then a much faster way to go is with Data Text to Column so Alt+DE.
I’m going to say the data is Delimited and step 2, I’m going to say it’s Delimited by the – and very carefully, in step 3, I’m going to say, “Hey, we do not want to import the first column, we only want to import the second column and the destination is not going to be Column A, it’s going to be Column B.” Click Finish and we adjust the suffix.
Now, if something changes here, it won’t change there.
The same thing with this one, this is an Excel 2013 trick using Flash Fill, it creates a… They could see the Flash Fill is there but then they decided against it.
So, we’re going to come back here to data and Flash Fill.
Also, make sure that they get the B19 correct.
It’s strange that they could sense there was an answer there but then they must not have been sure enough, so they backed out.
Again, this is only in Excel 2013.
And in the example that Michael sent, his values before the – were always exactly the same.
If that happens to be the case, I wasn’t sure from Michael’s data if I was just seeing a small portion of it or not.
We can actually just count here that we want to start in =MID(A2,8,10) and that will reliably work if you know the values before the – are always the same.
Well, hey, I want to thank Michael for sending that question in.
I actually met Michael at one of my live Power Excel seminar so check out the one in Tampa.
I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel.
