Look Who it Was Random video

Excel - VLOOKUP for Airline and Flight Number Combined - Episode 523

674 views · Published 21 August 2009 · 2:43 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Joining Text in Excel: Concatenation | MrExcel Netcast.

Welcome back to the MrExcel netcast! In today's episode, we'll be discussing a question sent in by JJ from Alaska. If you have a question for the podcast, don't hesitate to leave us a voicemail or send us an email. We love hearing from our viewers and we'll do our best to answer your questions in a future podcast.

JJ works in tourism and has been struggling with manifests sent by the home office in Miami. The airline is listed in Column A and the flight number in Column B, but in order for JJ to use the VLOOKUP function, he needs both of these pieces of information in a single cell. So, how can we join text together in Excel? The answer is concatenation, which is just a fancy word for joining text.

To join text in Excel, we use the ampersand symbol instead of the plus sign. In JJ's case, we can use the formula =A2&B2 to combine the values in Column A and B. However, JJ mentioned that he needs a space between the airline and flight number. To add a space, we can edit the formula to =A2&" "&B2. This will add a space between the two values. Remember to hit control+Enter to accept the formula and then double click the fill handle to copy it down to all the cells.

Now, you may be tempted to delete Columns A and B since we have the desired result in Column C. However, if we do that, everything will disappear. To avoid this, we need to convert the formulas in Column C to values. There are a few ways to do this, but my favorite is to right click on the right edge of the selection, drag right, drag left, and then choose "copy here as values only" from the shortcut menu. This will convert the formulas to values without deleting the original data.

Once we have the values in Column C, we can safely delete Columns A and B. Now, we have the data exactly how JJ needs it to perform a VLOOKUP. We hope this tip helps you in your own Excel projects. Thanks for tuning in to another netcast from MrExcel. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) JJ's issue with airline manifests
(00:37) Using concatenation to join text
(00:48) Explanation of concatenation
(00:58) Formula to join values in columns A and B with space between
(01:08) Editing the formula for desired result
(01:26) Potential mistake to avoid
(01:39) Making values in column C permanent
(01:49) Different methods for copying and pasting values
(01:59) Shortcut for pasting values only
(02:17) Deleting unnecessary columns
(02:33) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial


This video answers these common search terms:
Airline
Column A
Column B
Concatenation
Copy and paste special values
Flight number
Joining text
Manifests
Text values
Tourism
VLOOKUP


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152573/



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!

More from this channel