David asks how to use Text to Columns to grab the right-most two characters from a column. Episode 1158 shows you how.
Transcript of the video:
MrExcel podcast is brought to you by “Easy-XL”!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question sent in by David.
David, this is a great question!
He says “Why can't Text to Columns, Fixed Width go right-to-left?” And I said “David, I don't understand why you would want to do that?” And he said “You know, hey, it's pretty simple, I have this 2-character suffix at the end of these part numbers, I need to grab those 2 rightmost characters, but I don't know how long the data is.” And he's absolutely right, if we go in fixed width, there's no way in Step 2 to say that we want to go from the right edge of the data.
So we're stuck doing something relatively painful, =RIGHT of this data,2 , will get me those two characters.
Alright, that's simple enough, but then to get the rest of the stuff, oh!
Well, OK, =LEFT of A2, I want the length of A2, minus 2, and copy that down, and you know, of course, then you have your answer.
Need to convert those formulas to values, so Ctrl+C and then Home, Paste, Paste Values, or whatever method you like to use.
So David, that, unfortunately, is what we're stuck with.
Now, David said “Hey, why don’t you ask Microsoft to do it the other way, and I'll certainly pass that along.” But you know, it might be 4 versions of Excel, you know, 2020, before we ever see them add that in.
So David, thanks for sending that question in, thanks to you for stopping by, we'll see you next time for another netcast from MrExcel!
David, that problem would be easy if you had Easy-XL!
Columns, Split Columns, split the ID column, from the right-hand side, Number of Characters: 3, Create a Remainder Column, Finished.
There's your right 3 characters, there's everything else.
Great way to go: www.easy-xl.com
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question sent in by David.
David, this is a great question!
He says “Why can't Text to Columns, Fixed Width go right-to-left?” And I said “David, I don't understand why you would want to do that?” And he said “You know, hey, it's pretty simple, I have this 2-character suffix at the end of these part numbers, I need to grab those 2 rightmost characters, but I don't know how long the data is.” And he's absolutely right, if we go in fixed width, there's no way in Step 2 to say that we want to go from the right edge of the data.
So we're stuck doing something relatively painful, =RIGHT of this data,2 , will get me those two characters.
Alright, that's simple enough, but then to get the rest of the stuff, oh!
Well, OK, =LEFT of A2, I want the length of A2, minus 2, and copy that down, and you know, of course, then you have your answer.
Need to convert those formulas to values, so Ctrl+C and then Home, Paste, Paste Values, or whatever method you like to use.
So David, that, unfortunately, is what we're stuck with.
Now, David said “Hey, why don’t you ask Microsoft to do it the other way, and I'll certainly pass that along.” But you know, it might be 4 versions of Excel, you know, 2020, before we ever see them add that in.
So David, thanks for sending that question in, thanks to you for stopping by, we'll see you next time for another netcast from MrExcel!
David, that problem would be easy if you had Easy-XL!
Columns, Split Columns, split the ID column, from the right-hand side, Number of Characters: 3, Create a Remainder Column, Finished.
There's your right 3 characters, there's everything else.
Great way to go: www.easy-xl.com
