If you need to spice up a column in your report, Episode 508 will teach you how to add a dash to a column. Viewer George writes in to say that his vendor needs the orders transmitted with a dash in the middle of the part number, but Georges data does not have the dash. Learn how to use MID and LEFT and concatenation to put a dash in the middle.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in by George.
George says he has a column of data in column A that he needs to insert a dash in the middle of this column data.
When I talked to George some more, it turns out that he has a part number over here it has three characters and then four numbers.
When he wants to add an item description and unfortunately his table with the item descriptions has SKU as the three part numbers a dash and the four numbers.
Now, this happens all the time you get data from one system and from another system, someone uses a dash, someone doesn't use a dash.
So, what we can do is basically insert a new column.
I'll call it SKU 2.
What we want to do is grab the leftmost three characters from column A.
So, equal left of A2 comma 3.
That'll give us just the text prefix and then we'll use the ampersand sign (&) and in quotes, put quote, dash, quote and then another ampersand and now we need to grab the middle portion of column A.
We're gonna start at character four, for a length of four.
So, we use the MID function A2.
We want to start at four and go for a length of four and that basically will get us to the 5945 and you'll see here that I get JWH- 5945.
We want to copy that formula down so I double click the fill handle and it gets copied down then we can insert a new column and do the description.
Now, last Friday we talked about using vlookup with the true parameter at the end.
In this case we need to use the false parameter.
That says we need an exact match, so equal vlookup.
We'll go grab this SKU, that our lookup table is over here in column I and J.
Again, we want to make sure that's absolute.
So, I'll hit the [ F4 ] key to put the dollar signs in second column and then the word false.
Basically, we'll grab our description for us copy that down double click the border between C and D and we've filled in all of the SKU.
This is a very common situation, where you're trying to do a vlookup, one data set, the table might have an extra dash or leading or trailing spaces.
If you need to get rid of leading or trailing spaces, I use the trim function.
It's very common that we have to go through and do some little tweak to the data either adding a dash or taking out spaces, in order to get the vlookup to match, bit frustrating.
You can see that JWH 5945 is the same as JWH-5945 but for Excel we have to get those exactly equal.
Hey, thanks for stopping by, we'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today's question sent in by George.
George says he has a column of data in column A that he needs to insert a dash in the middle of this column data.
When I talked to George some more, it turns out that he has a part number over here it has three characters and then four numbers.
When he wants to add an item description and unfortunately his table with the item descriptions has SKU as the three part numbers a dash and the four numbers.
Now, this happens all the time you get data from one system and from another system, someone uses a dash, someone doesn't use a dash.
So, what we can do is basically insert a new column.
I'll call it SKU 2.
What we want to do is grab the leftmost three characters from column A.
So, equal left of A2 comma 3.
That'll give us just the text prefix and then we'll use the ampersand sign (&) and in quotes, put quote, dash, quote and then another ampersand and now we need to grab the middle portion of column A.
We're gonna start at character four, for a length of four.
So, we use the MID function A2.
We want to start at four and go for a length of four and that basically will get us to the 5945 and you'll see here that I get JWH- 5945.
We want to copy that formula down so I double click the fill handle and it gets copied down then we can insert a new column and do the description.
Now, last Friday we talked about using vlookup with the true parameter at the end.
In this case we need to use the false parameter.
That says we need an exact match, so equal vlookup.
We'll go grab this SKU, that our lookup table is over here in column I and J.
Again, we want to make sure that's absolute.
So, I'll hit the [ F4 ] key to put the dollar signs in second column and then the word false.
Basically, we'll grab our description for us copy that down double click the border between C and D and we've filled in all of the SKU.
This is a very common situation, where you're trying to do a vlookup, one data set, the table might have an extra dash or leading or trailing spaces.
If you need to get rid of leading or trailing spaces, I use the trim function.
It's very common that we have to go through and do some little tweak to the data either adding a dash or taking out spaces, in order to get the vlookup to match, bit frustrating.
You can see that JWH 5945 is the same as JWH-5945 but for Excel we have to get those exactly equal.
Hey, thanks for stopping by, we'll see you next time for another netcast from MrExcel.
