The analyst's CORNER

Using text functions to create VLookup values

Recently we were completing a submission for a major Canadian survey. The vendors had thoughtfully recoded all their job codes which of course caused some distress for our internal tools. The job codes changed from a form like 0021, 0125 to a code incorporating both job function and job number, something like AD for administration and 12 for the job code. Unfortunately for us we coded the new jobs in the form AD12 and the vendor had added a hyphen creating the following form: AD-12. In order to use a Vlookup to fill their submission data sheet, we needed exact mapping. Oops what a pain.

We could not use an edit/replace because the first two letters changed with each job family. Thankfully Excel’s text functions make adding the hyphen a relatively simple task. We used a combination of the concatenate, left, and mid text functions to parse the AD12 and create AD-12.

We combined the three text functions below:

• The concatenate function joins text into a single string. Text or numbers from cells are separated by commas, functions work within the concatenate function so you nest computations. The concatenate function has the form: concatenate (text1, text2, text3…). Actual text string that is constant such as a hyphen can be added to the mix. These must be bracketed by double quotes to tell the function the input is text.
• The Left function parses text starting from the Left and selects the characters to extract based on the number input. The form for the Left function: =Left(target cell, number of characters). Left(W20,2) literally means take the first 2 characters from cell W20.
• The Mid function is pretty neat. It enables selection within a text string. The form for the Mid function: =Mid(target cell, start character, number of characters to the right). Mid(W20,3,5) means go to the 3 rd character in cell W20 and select characters 3 through 8 (3+5).
• Nesting the Left and Mid functions within the Concatenate function gives the following form:

=concatenate(left(W20,2),”-“,MID(W20,3,5))

• Autofill the nest function for all cell in column X.
• If you’ve done the Autofill correctly the column cells will remain highlighted and you can select Copy for all the X column cells
• Now select cell W20 and use the Edit/Paste Special using the option “Values Only” to paste the concatenated text into the original location for job codes
• You are now ready to prepare the Vlookup to merge the submission data from your spreadsheet to the vendor submission form.