JJ writes in with a question from Alaska. His home office keeps sending him data with airline in column A and flight number in column B. In order to use this data, he needs to join both values into a single cell. Episode 523 shows how to use concatenation to solve the problem.
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:
Hey all right, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today we have a question send in by JJ from Alaska.
If you have a question for the podcast, either leave us a voicemail or drop us an email and we'll get to you on a future podcast.
JJ says he works in tourism and the home office in Miami keeps on sending them manifests showing the airline in Column A and the flight number in Column B.
and unfortunately in order for him to do the VLOOKUP, he needs both of those joined into a single cell.
Well, to add things together that are text instead of using a plus sign we have to use the ampersand.
Now, this is called concatenation a big long word that basically means joining text together.
So, what we want to do is we want to take =A2&B2.
And it will smash the values in column A with the value in Column B.
Now, in JJ's email he said that he needs those separated by a space.
So, I gonna go back and edit that formula you use equal A2 ampersand and then in quotes, quote space quote ampersand B2, that'll basically take whatever is in A2 join it with some text in quotes in this case It's just a space and then B2, hit control+Enter to accept that and then I'll double click the fill handle to copy that down to all our cells.
Now, It's real tempting at this point to say well hey, I have exactly what I need in column C.
So, I don't need this data in Column A & B anymore, and we hit delete and unfortunately then everything disappears well at that point we hit control+Z to undo.
If you want to make these values in Column C , B values instead of live formulas we have to copy and paste special values.
Now. there's several different ways to do this we can use edit copy and then edit paste special values.
I want to show off my favorite way to do this and this was from someone in row 2 in Columbus, Indiana.
Basically, you want to right click on the right edge of the selection drag right, drag left, let go copy here as values only this shortcut menu only pops up after you right click and drag somewhere and drag back.
So now, we have the values in Column C. We can go ahead and delete columns A and B and everything is good.
We now have the data exactly like JJ needs to do it.
So, that way you can go on and do a VLOOKUP.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today we have a question send in by JJ from Alaska.
If you have a question for the podcast, either leave us a voicemail or drop us an email and we'll get to you on a future podcast.
JJ says he works in tourism and the home office in Miami keeps on sending them manifests showing the airline in Column A and the flight number in Column B.
and unfortunately in order for him to do the VLOOKUP, he needs both of those joined into a single cell.
Well, to add things together that are text instead of using a plus sign we have to use the ampersand.
Now, this is called concatenation a big long word that basically means joining text together.
So, what we want to do is we want to take =A2&B2.
And it will smash the values in column A with the value in Column B.
Now, in JJ's email he said that he needs those separated by a space.
So, I gonna go back and edit that formula you use equal A2 ampersand and then in quotes, quote space quote ampersand B2, that'll basically take whatever is in A2 join it with some text in quotes in this case It's just a space and then B2, hit control+Enter to accept that and then I'll double click the fill handle to copy that down to all our cells.
Now, It's real tempting at this point to say well hey, I have exactly what I need in column C.
So, I don't need this data in Column A & B anymore, and we hit delete and unfortunately then everything disappears well at that point we hit control+Z to undo.
If you want to make these values in Column C , B values instead of live formulas we have to copy and paste special values.
Now. there's several different ways to do this we can use edit copy and then edit paste special values.
I want to show off my favorite way to do this and this was from someone in row 2 in Columbus, Indiana.
Basically, you want to right click on the right edge of the selection drag right, drag left, let go copy here as values only this shortcut menu only pops up after you right click and drag somewhere and drag back.
So now, we have the values in Column C. We can go ahead and delete columns A and B and everything is good.
We now have the data exactly like JJ needs to do it.
So, that way you can go on and do a VLOOKUP.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
